Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find matching values
I regularly have to compare two spreadsheets of account names to find the
matching values. Each spreadsheet has account names in one columns, but then other columns will have different info, which I have to merge together if there are matches. I usually line up the columns and then color one set red and them put them into the same worksheet and sort by name and scan visually for matches, then do a lot of cutting and pasting... Question #1 - Is there a way to find all the matching values in one column? Say column A has two instances of Wal-Mart, how can I locate them in 1,000 lines? Is there a way to move them, and their attendant data into another column or table once I do? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find matching values
If your second spreadshet has unique data (each account name has one row) you
could use vlookup to pull the data into the first. example to get data from 4th column of 2nd spreadsheet: =VLOOKUP(A2,Sheet2!A1:F100,4,false) and change the column number accordingly. Another idea is to use the countif function to check if there are entires on sheet2. =if(COUNTIF(Sheet2!A1:A100,A2)0,"Match found", "no match") And that would at least give you a quicker way to find matches. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "chris" wrote: I regularly have to compare two spreadsheets of account names to find the matching values. Each spreadsheet has account names in one columns, but then other columns will have different info, which I have to merge together if there are matches. I usually line up the columns and then color one set red and them put them into the same worksheet and sort by name and scan visually for matches, then do a lot of cutting and pasting... Question #1 - Is there a way to find all the matching values in one column? Say column A has two instances of Wal-Mart, how can I locate them in 1,000 lines? Is there a way to move them, and their attendant data into another column or table once I do? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find matching values
thanks Luke!
What if I just put them together and search one column? What would the function for that look like (again, I am searching for many pairs of matches which I don't know the value of ahead of time) Thanks, Chris "Luke M" wrote: If your second spreadshet has unique data (each account name has one row) you could use vlookup to pull the data into the first. example to get data from 4th column of 2nd spreadsheet: =VLOOKUP(A2,Sheet2!A1:F100,4,false) and change the column number accordingly. Another idea is to use the countif function to check if there are entires on sheet2. =if(COUNTIF(Sheet2!A1:A100,A2)0,"Match found", "no match") And that would at least give you a quicker way to find matches. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "chris" wrote: I regularly have to compare two spreadsheets of account names to find the matching values. Each spreadsheet has account names in one columns, but then other columns will have different info, which I have to merge together if there are matches. I usually line up the columns and then color one set red and them put them into the same worksheet and sort by name and scan visually for matches, then do a lot of cutting and pasting... Question #1 - Is there a way to find all the matching values in one column? Say column A has two instances of Wal-Mart, how can I locate them in 1,000 lines? Is there a way to move them, and their attendant data into another column or table once I do? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
Find matching values in two different columns | Excel Worksheet Functions | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
Macro to find matching date and copy values to another sheet | Excel Discussion (Misc queries) | |||
find a cell matching separate column and row values | Excel Worksheet Functions |