Merging data using unique column identifier?

**jjinca** · 08-09-2007, 07:03 PM

**vane0326** · 08-09-2007, 08:24 PM

Try....

Inputing these formulas into the Define name range box.

Insert>Define>Name:

Name: Array:

Refers to: =SUMPRODUCT((Sheet1!$A$1:$A$20<>"")/COUNTIF(Sheet1!$A$1:$A$20,Sheet1!$A$1:$A$20&""))

Name: Array2:

Refers to: =COUNT(1/(IF(Sheet1!$B$1:$B$20<>"",MATCH(Sheet1!$B$1:$B$20,IF(Sheet1!$A$1:$A$20=Sheet1!$E2,Sheet1!$B$1:$B$20),0)-1+ROW(Sheet1!$A$1)=ROW(Sheet1!$A$1:$A$20))))

Then input formula in cell E2 and copy down.

=IF(ROWS(E$2:E2)<=Array,INDEX($A$1:$A$20,MATCH(0,IF($A$1:$A$20<>"",COUNTIF($E$1:E1,$A$1:$A$20)),0)),"")

Then also input formula in cell F2 and copy across and copy down.

=IF(COLUMNS($E$2:E2)<=Array2,INDEX($B$2:$B$20,SMALL(IF($A$2:$A$20=$E2,IF(MATCH($A$2:$A$20&$B$2:$B$20,$A$2:$A$20&$B$2:$B$20,0)=ROW($B$2:$B$20)-ROW($B$2)+1,ROW($B$2:$B$20)-ROW($B$2)+1)),COLUMNS($F$2:F2))),"")

I left a sample workbook below.

Hope it helps!

**jjinca** · 08-10-2007, 10:55 AM

Originally Posted by vane0326

Try....

I left a sample workbook below.

Hope it helps!

Hey Vane... thanks for taking a crack at it! I'll see if I can make some use of the formulas you've left above.

Unfortunately, your workbook example was not set up correctly for the problem. There should be 4 columns (ID,FIELD1,FIELD2,FIELD3)

In your workbook example, the result for the second list for ID 99999 "theother" did not appear in the results.

Also, because you are using just ID, FIELD1 for your test, the results are then stored in the first available field on the final output - rather than the field that it should be stored in.

**jjinca** · 08-10-2007, 10:57 AM

If I can't do this in excel, I guess I could pop it out to .csv & then manipulate and merge the lists in unix, then reimport the merged .csv back into excel.

I'm sure Excel can do this somehow though... just not sure how

**vane0326** · 08-10-2007, 10:14 PM

If possible can you post the workbook with the expected results?

**FrankBoston** · 08-11-2007, 05:32 PM

JJinca, you have an interesting problem that I have seen a few times in one form or another. Could you use a VBA solution if I posted one?

**Vikes23** · 08-13-2007, 10:49 AM

I am facing the exact same problem and I would be grateful if you could post a VBA solution

**FrankBoston** · 08-16-2007, 03:06 PM

Problem:
A unique ID is associated with values in different sheets or tables. Bring the values
together on one row for each ID, leaving blank cells for missing values.

Please Login or Register  to view this content.

**jjinca** · 08-16-2007, 04:23 PM

I wish I could use your example above, but I'd need step-by-step instructions on how to use the code above.

I know that you put it in the MACRO area, but I'm not sure exactly where and what to do.

Right now I'm just doing as I suggested above: export to .csv, merge master data with new data (keeping .csv format), import merged file back into excel.

**FrankBoston** · 08-16-2007, 04:41 PM

Try downloading the attached Test.zip and move AssembleData.xls to your directory. This gives a working example and has all of the VBA code installed and working.

Insert additional sheets and do your work on them, no VBA understanding needed. You can move your rearranged data back to the workbooks that you normally use.

My approach uses worksheet functions (in a tricky way) to interface with the VBA code, so you probably do not need to do any VBA modifications, at least for your needs as you posted them.

You will see that there is an Assemble Data Do toolbar button in that workbook, ready to do the data movement. And, read the attached documentation for explanations.

**jjinca** · 08-16-2007, 05:30 PM

I'm sure this will help someone... but it's not quite what I need.

See, I have a master list of ids, (1 per row) with all possible horizontal fileds. I am merging data into this master list from another list.

So for example if my master list has 1000 rows (1000 unique ids), with 50 fields each, then to use your script above, I would have to first break out the existing master list into (up to 50,000 rows) of:

ID# | FIELD#

They would also strictly need to be in sequential order (which you noted) or it will create separate entries.

From what I understand of this script, it would also be to go back and add more data to the various rows of the existing master list later.

--------------------------------------

The best way I can explain of what I am looking for is....

There is a master list containing:
"UNIQUE_ID","FIELD1","FIELD2","FIELD3","FIELD4","FIELD5","FIELD6","FIELD7"

A unique_id will always exist in the master list, but not all fields will be filled.

There are multiple merge lists consisting of entries like:

mergelist1:

"UNIQUE_ID","","FIELD2","","FIELD4","","FIELD6","FIELD7"

mergelist2:

"UNIQUE_ID","FIELD1","","","FIELD4","FIELD5","FIELD6","FIELD7"

etc..

So basically we would want to LOOP EACH LINE of a mergelist against EACH LINE of the masterlist to find the current unique_id we're processing in the mergelist, within the masterlist. If it finds the unique_id, in the predefined column, then it would INSERT or UPDATE (to use db terms) the new data from the mergelist.

If the unique_id from the mergelist DOES NOT EXIST in the masterlist, then a new entry would be created in the masterlist for that unique_id - populated with whatever "fields" are present in the mergelist.

I currently have the mergelist on the same worksheet as the masterlist, with the "fields" of the mergelist just being the same columns as those defined in the masterlist above. I was hoping to be able to add my mergedata, process it into the masterlist above, then just remove my mergedata -- left with just updated masterlist.

I don't understand why excel can't do something like this out of the box.

I'd think it's a function people would need often.

**rylo** · 08-17-2007, 12:58 AM

Hi

Can you see if this is getting closer to your structure and requirements.

rylo

**jjinca** · 08-17-2007, 01:08 PM

Originally Posted by rylo

Hi

Can you see if this is getting closer to your structure and requirements.

rylo

Rylo... awesome! ...and very close!

I've done a little testing with it, and the only problem is when I introduce a new "user id" in the mergelist, that does not exist in the masterlist, it does not create a new entry in the mergelist.

Other than that, it seems to do exactly what I want!

**jjinca** · 08-17-2007, 01:19 PM

Hmm one other problem I guess...

Say on mergelist you have data like this:

userid field7 field8

12345 yes yes
22222 yes
33333 yes
55555 yes no

So the problem is with 22222 and 33333 and the blanks...
We do not exactly want to import a blank for field8 for 22222 and 33333.

But I see it is just part of "UPDATE"... if it's blank, it updates with what it is given (a blank space).

So, maybe it could be modified to not import a blank?

If user wants to import "blank" they could use a space for the field, or better idea... maybe have the script look for: *BLANK* in the mergefile field if the user wants to import a empty space for that field?

Anyway, I think this script will be useful to many in the forum

**rylo** · 08-17-2007, 09:20 PM

Hi

Try this

1) I thought from your post that the master list would already contain all the User IDs. I've updated the code to add any new User ID.

2) Should not update any blank entries.

rylo

**jjinca** · 08-20-2007, 11:17 AM

Hey thanks for the update!

However, there is still a bug ....

Imagine I have the following on a mergelist....

USER ID LOGINID
22222 ntl22222
12345 ntL1235
11111 ntl11111
55555 ntl55555
22222 ntl22222BB

USER ID SOCKS SHOES AGE

22222 no no 99
123 no NO 123
5150 yes yes 555
456 yes yes 26
789 yes yes 25
234 yes yes 24

There are two problems....
#1: "123" and "234" do not get added, because (I think) they are matching with 12345 which occurs earlier in masterlist. However, even if this is the case, the new field data is not getting added... which leads to the second problem. I think to fix the first problem you must not look for such a broad match, but "exact match" of user id field.

#2: When a new field is added... like "456" "789" ... it doesn't update the field data that was supplied with it. Odd, because if I run macro again, it still doesn't include that data.

**rylo** · 08-20-2007, 06:35 PM

Hi

OK, try this.

rylo

**jjinca** · 01-14-2008, 07:24 PM

Originally Posted by rylo

Hi

OK, try this.

rylo

Hey Rylo... what would I change to get it to start looking for and dumping to "USER ID" on the Xth row of "Master List"... for example, if I had black rows, the n the header row with "USER ID" on row #4, then data begins on A5.

I was not easily able to figure out what to change.

Please Login or Register  to view this content.

**rylo** · 01-14-2008, 10:23 PM

HI

Sorry, but I don't follow what you want to do. Can you put up an example file showing the before and after situations, with explanations on why what went where.

rylo

**Mayanja** · 05-14-2013, 07:55 AM

I guys I know Its 2013 and you may have solved this problem but Am have the same problem I have to: I have to Combine 3 files in a database utilizing stock_code as the unique identifier: and I am lost right I tried to copy them and past but til I can not get the unique identifier to merge... any help