Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Read Input file and highlight matches in excel
Hello All,
I was wondering if it was possible to read a text input file and compare that list to see if it exists in excel. If it does, can we highlight the entire row. This way, I can delete the un-lighted rows. The row that we want to compare or match is in Column A. Example: Text Input file: --------------- OR-MyTestfile OR-StateofUnion OR-WhereAreYouFrom Excel Spreadsheet -------------------- A B C D 1 OR-MyTestFile FileName Location Size 2 OR-YourNameIs FirstName EastWing 10mb 3 OR-StateofUnion WhichState City Zipcode 4 OR-HowOldAreYou Age DOD Year 5 OR-WhereAreYouFrom Born Date Time With the above example, the following ENTIRE ROW if possible, would be highlighted (1,3,5) Side note: The Input text file is created by manually extracting those names from muttilple sources. End Results: Only keep those rows that are highlighted Any and all help in this matter is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Read Input file and highlight matches in excel
If you can get your data into two different worksheets of the same workbook, you
could add some headers and use a formula to mark the matches. Say Sheet2 is the text input file version and Sheet1 contains your data. Insert a new column B: Add a header in B1 In B2: =isnumber(match(a2,sheet2!a:a,0)) and drag down You'll see True or false for each line. Apply data|filter|autofilter to column B. Show the lines you want to delete (False) Delete the visible rows remove the autofilter. Chip Pearson has lots of techniques to work with duplicates: http://www.cpearson.com/excel/Duplicates.aspx Argus Rogue wrote: Hello All, I was wondering if it was possible to read a text input file and compare that list to see if it exists in excel. If it does, can we highlight the entire row. This way, I can delete the un-lighted rows. The row that we want to compare or match is in Column A. Example: Text Input file: --------------- OR-MyTestfile OR-StateofUnion OR-WhereAreYouFrom Excel Spreadsheet -------------------- A B C D 1 OR-MyTestFile FileName Location Size 2 OR-YourNameIs FirstName EastWing 10mb 3 OR-StateofUnion WhichState City Zipcode 4 OR-HowOldAreYou Age DOD Year 5 OR-WhereAreYouFrom Born Date Time With the above example, the following ENTIRE ROW if possible, would be highlighted (1,3,5) Side note: The Input text file is created by manually extracting those names from muttilple sources. End Results: Only keep those rows that are highlighted Any and all help in this matter is greatly appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
How do you save an excel file to be read as IBM-type text file ? | Excel Worksheet Functions | |||
Pasword protected Excel file encrypted, how do I read this file? | Excel Discussion (Misc queries) | |||
"Unable to read file" error message when opening a Excel file that contains a PivotTable report. | Charts and Charting in Excel |