 Wily_OnePremium
join:2002-11-24
San Jose, CA | [Excel] Pulling my hair outI have tried in earnest to find a solution to this, but the Interweb is letting me down. I have a spreadsheet that has two columns of data that only partially share the same content. What I need to do is line it up so the same name is on the same row, and any rows that don't match are left with a blank on the other "side". The data in Col A is a partial match but not necessarily a subset of the data in Col D, and vice versa.
Here's what my data looks like:
Col. A Col. B Col. C Col. D Col. E
------ ------ ------ ------ ------
abcdef status data ghifkl notes
ghifkl " " notint "
stuvwx " " stuvwx "
yzabcd " " rlist "
What I want is:
Col. A Col. B Col. C Col. D Col. E
------ ------ ------ ------ ------
abcdef status data <-- no match on right
ghifkl " " ghifkl notes <-- match lined up
notint " <-- no match on left
stuvwx " " stuvwx " <-- match lined up
yzabcd " " <-- no match on right
rlist " <-- no match on left
I've tried MATCH, VLOOKUP, and so on, but I'm not getting what I need. Appreciate any help, thanks. |
|
 JohnInSJPremium
join:2003-09-22
San Jose, CA
 ·PHONE POWER
 ·Comcast
| looks like you want a database, that can do a join.
Maybe this page will help doing that in excel
»www.digdb.com/excel_add_ins/join···s_lists/
--
My place : »www.schettino.us |
|
 PhoenixDown-- Wants FIOSPremium
join:2003-06-08
Fresh Meadows, NY
kudos:1 | reply to Wily_One
DB might be easiest .... but here's some food for thought if you want to do this via excel (which I sometimes prefer to do for various reasons).
1. You need an exact match for A and D, in my example I had something like
A: 123456-789 abcd
D: 123456-789 (or) 12-34-56
Stuff like that ....
You will need to find a common element.
* Do the first six characters match?
* The middle six characters?
* Will that create incorrect duplicates because 12345678 does not equal 12345612?
You may need a dozen different formula's to strip out characters and get the format right. If you have some examples you want to send me via PM I'll take a look.
2. Next step.
* Cleaned up (step 1) Copy of A and paste it into a new worksheet.
* Cleaned up (step 1) Copy of D and past it under that.
* Use the remove duplicates function to get a single "Master" list.
3. Add your fields across B, C, E.
4. Now your basically going to do two vlookups.
* One against Cleaned Up A to pull in columns B and C
* Second is against Cleaned up D to pull in Col E.
Additional recommendations:
I've had to do things like this where someone asked me to pull in more info afterward. You may want to add a new column for a unique id #. It can be a number or a concatenate of several existing fields (ie: Last_Name~First_Name~Zip_Code)
I find it makes it easier if I need to add a new set of data.
Like, your spreadsheet may be what I put together but that data came from four other sources with twenty more fields each. Now someone wants field X and I now have to step back through three spreadsheets to pull that in.
Having something to use as a unique id might make it easier to pull in those fields later from the source.
--
1/22/2012 Delegate Count
Newt 25 | Romney 14 | Ron Paul 10 | Santorum 8
|
|
