#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
One of possible ways to do this!
|
#4
|
||||
|
||||
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. |
|
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 |
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 |
Count from another workbook | teza2k06 | Excel | 3 | 03-19-2015 01:20 PM |