Strange lookup request

[hedgie]

I am not really sure how to describe this but I will try. I have a nice tidy data table that includes an identifying number for each item. This Identifying number represents a slew of other data which needs to be incorporated. The other data is able to be put into another excel file, but in a vertical format with 2 columns the first listed below and the second has the data as follows:
Identifying number 1
info A
info B
info C
info D

Identifying number 2
info A
info C
Info E
Info F

Identifying number 3
info B
info C
info F
info G

etc....

My problem is how do I organize this data so that I can imort it or match it to give the info in the original data table?

I think the cleanest thing to do is create a new vlookup table, but since I have over 500 identifying numnbers I am not sure how to transpose this so that all the info A,B,C will find their way to the right column. Meaning that if I transposed the data above, #1 would have A in column 2 and B in COlumn 3 but number 3 would have B in column 2.

Any thoughts?

 

[nbrcrunch]

hedgie:

I've seen your post and read it several times. I noted this morning that you had 60 people view the post without any replies so I decided to post....

You need to provide more information on:
1. How your original data is layed out
2. Be explicit with the number of files you are dealing with. Use fake names to differentiate if possible. (From what I can decipher, you mentioned at least three or four different files in unspecific terms.)
3. Demonstrate how you want the data laid out in the target file using (again) good examples. (As near as I can figure, you have four fields for each record: (ID, Info1, Info2, Info3, Info4) It would also be helpful to know what kind of data (text, dates, numeric values, etc) is being manipulated.

 

[PaddyD]

Hi,

If I've got you right, an example of the sort of thing you're trying to do is here:

http://www.mrexcel.com/board2/viewtopic.php?t=180186

...it addresses the 'records have different numbers of data elements' issue, but not the 'and each element needs to go in the right place' issue (which I assume exists because of the a,b,c indexing).

As per nbrcrunch's reply, it would help to see a real example.

 

[hedgie]

Thank you - Pady's link is sort of what I was looking for. It ends up a data scrubbing expert at work was able to clena up the data perfectly. THanks again for your help, next time I will try and post a better example.