I have two spreadsheets. They share data in four columns (first name, last name, title, company). One spreadsheet also has an ID number column, the other has a Business Type column.
The rows don't synch up (there are 32,000 rows), and the number of rows in each sheet is not an exact match. I need to combine these spreadsheets into a single document so that each row has all of the relevant data--I need to filter/match on first name, last name, title, and company, and then add the business type and the ID number all into a single spreadsheet.
Can anyone help? I'm totally new to macros, I've worked with formulas somewhat, and I don't have MS Access. Using Excel 2010.
Thanks!
Make backup copies of both sheets first.
For a non-macro attempt:
Make it so both sheets have the same column headers in the same order... so add the ID column to the sheet that doesn't have it and add the Business type to the other sheet that doesn't have it.
Then run a formula in each sheet that looks at the other sheet.
E.g
=INDEX('Sheet1'!$E$2:$E$1000,MATCH(1,INDEX(('Sheet1'!$A$2:$A$1000=A2)*('Sheet1'!$B$2:$B$1000=B2)*('S heet1'!$C$2:$C$1000=C2)*('Sheet1'!$D$2:$D$1000=D2),0),0))
where Sheet1 is the "other sheet", E2:E1000 contains the ID or Business Type.. (note in the other sheet the formula should index the next column F2:F1000) and A2:D2 contains the shared information.
Adjust ranges and references to suit and after doing this in both sheets, then copy one sheet and Paste Special|Values over itself.... and repeat for other sheet.
Now all the rows of one sheet and paste to the bottom of the other sheet.
Then apply Data|Filter|Advanced to the entire range and select Unique Values only.
Click Ok, it should filter down unique items.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks