Apr 01 2018
09:31 AM
- last edited on
Jul 25 2018
11:32 AM
by
TechCommunityAP
Apr 01 2018
09:31 AM
- last edited on
Jul 25 2018
11:32 AM
by
TechCommunityAP
My excel spreadsheet has ~50000 rows of names and ~25 columns of relevant information.
I would like to search, find and highlight ~2000 (of the 50000) names and relevant column information.
What is the easiest way to do it?
Any help in this regard is highly appreciated.
Thanks,
V
Apr 01 2018 10:04 AM
Hi Vivek,
If the 2000 names are separate somewhere, and these names are unique identifiers, you can depend on them to return each name's info from the master dataset!
This is by using some lookup functions such as VLOOKUP, INDEX/MATCH.
If so, please attach a sample of them to show you how to do that.
Apr 01 2018 01:15 PM
If you have the list of 2000 names somewhere in the workbook, you can add a helper column to the original data table with a formula along the lines of
=if(countif(Namelist!$A:$A,[@Name])>0,True,false)
So, look at the name in the current table and find it in the list on the other sheet. Return TRUE if found and FALSE if not.
Now you can use conditional formatting on your table, using a formula that looks at the value in the helper column. Select the table and, assuming the current cell is in row 2 the Helper column is in column Z, use a formula like this
=$Z2
The value of Z2 is either TRUE of FALSE, so you don't need a complex formula. Just make sure that the column reference has a $ sign and the row number has NO $ sign in front of it. Then select a fill color to highlight and apply the rule to your whole table.
With this approach, you can add more names to your list of 2000 and they will automatically be highlighted in the table.
Let me know if that works for you.
Apr 01 2018 01:17 PM
Hi Haytham,
Thanks for your response!
Please see the attached excel.
I have abridged my table here with ~1000 rows and 3 columns.
I need to find the 20 IDs highlighted in column 4 from column1.
How to use the index/match/vlookup in this case?
Apr 01 2018 02:04 PM
SolutionHello,
in the following screenshot, I moved the list to column F and created a helper column in column D with the formula
=IF(COUNTIF(F:F,A2)>0,TRUE,FALSE)
in cell D2 and copied down.
Then I selected the whole table from row 2 to the bottom and created a new conditional formatting rule with the formula
=$D2
I selected a yellow fill and confirmed all dialogs.
Apr 01 2018 08:33 PM
Worked really well!!!
Thanks for saving my weekend, and a lot of time!!!
Best regards,
Viv
Apr 01 2018 08:41 PM
Hi Vivek,
Please note that you have to use two techniques, one for highlighting, and one for lookup.
@Ingeborg Hawighorst has explained the first one.
But you don't have to create a helper column, you can directly put that formula into the Conditional Formatting rule.
With regards to the second one, you can depend on VLOOKUP function, with the support of COLUMNS function as follows:
=VLOOKUP($E2,$A$2:$C$1000,COLUMNS($A$1:B$1),0)
Where:
$E2 is the lookup value;
$A$2:$C$1000 is the lookup range;
COLUMNS($A$1:B$1) is column index number;
0 represents FALSE which is turn on the exact match.
NOTE: I've used COLUMNS function in column index number argument to make the column index number expandable as you drag the formula to the right so that you don't have to change it manually for each column.
I've updated the workbook with the solution, please find it in the attachments.
Hope that helps
Haytham
Apr 01 2018 02:04 PM
SolutionHello,
in the following screenshot, I moved the list to column F and created a helper column in column D with the formula
=IF(COUNTIF(F:F,A2)>0,TRUE,FALSE)
in cell D2 and copied down.
Then I selected the whole table from row 2 to the bottom and created a new conditional formatting rule with the formula
=$D2
I selected a yellow fill and confirmed all dialogs.