Excel 2007 : Help matching data based on item number

**SubtleFudge** · 04-08-2012, 12:55 PM

In worksheet X, there is a list of items starting in A1 that read something like this “78. Blah blah blah”, “56. Etc.” and so on. In worksheet Y there is a list in A1 that reads, “SE5r 3.50 0.555 0.666” and "SE78 0.4 0.98 1.4” in A2. There are dozens of item numbers and they are all out of order.

What I want to do is to keep “78. Etc” in worksheet X but find the item that starts with SE78 and match the numbers that come after it to the other worksheet. So my final product for the first row would be: A1 (78. Blah blah blah) B1 (0.4) C1 (0.98) D1 (1.4)

This is difficult for me partially because in worksheet Y the item number and numbers are all in one column instead of the multiple ones I’d like for them to be in. But I wouldn’t mind suggestions of how to simply match the numbers up by the item numbers – I’m pretty new to this. Thank you so much!

**Mallycat** · 04-08-2012, 08:09 PM

you need to post sample sheets, and a sample of what you want the output to look like, and make it clear where the data in the sample output came from. Then someone can help you

**FDibbins** · 04-08-2012, 08:42 PM

Have you tried to break up th long entries using "text-to-columns"? depending on how you set it up, it may be able to break your enties down into segments that will make it easier for you to find/do what you want?

I always make a copy of what i want to split up 1st tho, that way, if i screw it up (and it wont undo), i still have my original data.

Let me know how that works for you...or as suggested, upload a sample for us to look at

**SubtleFudge** · 04-08-2012, 11:23 PM

Alright, here is a sample. Worksheet 1 and 2 are what I currently have, except there are many more items than just four, of course, and remember that they are all out of order. Worksheet 3 is how I want to put the data from each worksheet together.

**Mallycat** · 04-09-2012, 01:29 AM

Do you know how to use VBA code? If so, this should do what you want.

Note, it relies on the data being in sheets with the exact names in your sample. Otherwise you will have to change the code. Also note there is no error checking, as you did not indicate a need.

Please Login or Register  to view this content.

I have posted the example sheet with the code for you to see how it works.

**SubtleFudge** · 04-09-2012, 03:58 PM

Hmm - I have never used VBA code. But I will check it out; thank you. If someone knows how to use Excel formulas to achieve the desired result, or could at least give me some general advice, I would appreciate it.

**SubtleFudge** · 04-10-2012, 04:42 PM

I am still working on this - if someone has any ideas, that would help a lot! I think it must be possible to use some combination of formula to search for the numbers after "SE" in Sheet 1 and import the rows if the number is in Sheet 2... I just can't figure it out.

I would prefer to learn VBA as a last resort, since I would love to be able to understand why I'm doing what I'm doing, not just get the desired result.