Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-02-2021, 01:03 AM
ganesang ganesang is offline Need to sort based on count from each workbook Windows XP Need to sort based on count from each workbook Office 2016
Competent Performer
Need to sort based on count from each workbook
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default Need to sort based on count from each workbook

Hi Guys

I have the workbook which contains numbers in column C.

If i put any number to search in cell G6, then have to lookup in column C and count and sort based on their count from each workbook with their names as follows,

If i put 6077 in G6 then lookup and return result as follows

B 1 A 0

Here A, B is the workbook names and numbers (1, 0) are count of 6077 from each workbook



Please find the attached workbook and advise.

thanks
Ganesan. G
Attached Files
File Type: xlsx Excel function help.xlsx (11.1 KB, 5 views)
Reply With Quote
  #2  
Old 09-15-2021, 07:12 PM
James Martin James Martin is offline Need to sort based on count from each workbook Windows 10 Need to sort based on count from each workbook Office 2019
Novice
 
Join Date: Sep 2021
Posts: 7
James Martin is on a distinguished road
Default

=COUNTIF(C36:C36,$G$6) put this in column G and drag down via the square at the corner of the cell.

=IF(G36=1,LEFT(A36,1),"do nothing") put this in column H and drag down as well.

You need to switch on filters at the top of the table, and ensure there are no blank rows in at least one column otherwise filters don't work. The if formula will only highlight the A or B if the number is identified as $ lock the cell reference, when dragging formulas to fill. If the look up value is variable in the table, I would have to reconsider.

You could just do a filter on the existing data to show the rows for the value highlighted without the formulas used.

Not really sure what you want, without really understanding your data. I do a lot of "house keeping" for a spreadsheet normally, with work around set ups before I even get started... Some formulas to think about if you have not used them.
Reply With Quote
  #3  
Old 09-15-2021, 10:11 PM
ArviLaanemets ArviLaanemets is offline Need to sort based on count from each workbook Windows 8 Need to sort based on count from each workbook Office 2016
Expert
 
Join Date: May 2017
Posts: 640
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

One of possible ways to do this!
Attached Files
File Type: xlsx Excel function help.xlsx (12.1 KB, 2 views)
Reply With Quote
  #4  
Old 09-17-2021, 08:47 AM
p45cal p45cal is offline Need to sort based on count from each workbook Windows 10 Need to sort based on count from each workbook Office 2019
Expert
 
Join Date: Apr 2014
Posts: 514
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

If you have a version of Excel which supports worksheet functions SORTBY and UNIQUE you could do it with a couple of helper cells K3 and L3.
Two solutions in the attached, one at cell G7 which puts the results in 2 rows, sorted, the other at cell G10 which puts the results in a single row of cells, as you have, sorted.

If you don't have those functions available to you a pivot table is a very easy solution. See cell F14. Choose your number in cell G14. This also sorts.

I've added a few rows of raw data for testing, tweaked a few values in the raw data too. Cell J1 shows a list of numbers for testing.

It's interesting to note that the pivot table honours the difference between '121' and '0121' whereas the formulae lump these together.
Attached Files
File Type: xlsx msofficeforums47599Excel function help01.xlsx (17.0 KB, 1 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to sort based on the length of cells ganesang Excel Programming 2 08-31-2021 09:58 PM
Sort pages based on header comphi Word VBA 0 09-22-2018 11:14 PM
Need to sort based on count from each workbook One macro to sort individual sheets in a workbook darbybrown Excel Programming 1 09-11-2016 12:51 PM
data entered in one workbook should be updated in other relevant workbook based on the date vedha Excel 0 04-24-2015 08:45 PM
Need to sort based on count from each workbook Count from another workbook teza2k06 Excel 3 03-19-2015 01:20 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft