Vlookup over multiple columns

[babypink2807]

Hi

I have a workbook with two worksheets. 1st worksheet (TAB1) has all my "machines" identified by its serial number, one record per machine.

On my second sheet (TAB2) has multiple machines to one record. Unfortunately, the data for the serial numbers (multiple) exports in to one column and are separated by a comma. I then convert the column to text and have ended up with an extra 32 columns most of which are serial numbers. So I have one record some with 32 columns (for each individual serial number) and some records from anything between 1 column and 32.

The goal is to match the serial number in TAB 1, to the serial numbers in TAB2 and pull through that data from TAB2. I have done it and it feels very long winded, and then I have to create a Power Query (Using youtube!) to bring all that data together

My question is, can Vlookup from TAB1, lookup a column with multiple serial numbers in? Then pull through that data or; can Vlookup look up multiple columns and pull the data through

I had to do this 2 weeks ago and took me 2 days to do, now they want it again with extra data and I am losing the will to live

Thank you

 

[Joe4]

Unfortunately, the data for the serial numbers (multiple) exports in to one column and are separated by a comma. I then convert the column to text and have ended up with an extra 32 columns most of which are serial numbers. So I have one record some with 32 columns (for each individual serial number) and some records from anything between 1 column and 32.

That is really a bad design to try to work with. It is going to make things very difficult and cumbersome.
What I would recommend doing is creating a VBA Script that writes out each Serial Number to its own row.
So instead of using Text to Columns to insert a bunch of new columns, you would use VBA to create multiple rows for each serial number.
So the number of columns would not change, and you just have a single column for Serial Number.
This will make it very easy to do your VLOOKUP, as you only need to look at one column.

 

[Fluff]

You can use vlookup with wildcards like

=Vlookup("*"&A2&"*",somerange,,,)

 

[babypink2807]

That is really a bad design to try to work with. It is going to make things very difficult and cumbersome.
What I would recommend doing is creating a VBA Script that writes out each Serial Number to its own row.
So instead of using Text to Columns to insert a bunch of new columns, you would use VBA to create multiple rows for each serial number.
So the number of columns would not change, and you just have a single column for Serial Number.
This will make it very easy to do your VLOOKUP, as you only need to look at one column.

Hi

it is, and its a really old CRM database, then contact details dont talk to the machine sale hence I have to download in to excel and try to match up the serial numbers. Sadly I am not experienced enough to use VBA, its way beyond me

 

[babypink2807]

You can use vlookup with wildcards like

=Vlookup("*"&A2&"*",somerange,,,)

Not sure I understand that to be fair, at the moment I am ploughing through 32 TABS, formatting the tables in preparation for the Power Query. If they ask me to do this again I may just have to slap them

 

[Fluff]

Which column has your concatenated serial numbers & which column do you want to return?

 

[babypink2807]

Which column has your concatenated serial numbers & which column do you want to return?

I now have TAB 1 with all my raw data. Plus now another 30 TABS, with all the same contacts, but serial numbers in column one, its difficult to explain. So eventually I have TAB 30 with say 5,000 contacts and one serial number in the column. TAB 2 I have 5,000 contacts but over 1000 serial numbers, TAB 3 I have 5000 contacts with 500 serial numbers and so on until I got to just one serial number. I then used a Power Query to look up all the sheets, linking the serial numbers and then it pulls through the data. Pain in the back side to be fair

 

[Fluff]

Can you please answer my question?

 

[babypink2807]

Can you please answer my question?

Apologies I am at work so cant respond immediately

TAB one has in column A my serial numbers in Tab two, column A I also have my serial numbers

 

[Fluff]

Ok, this will look at A2 & try to find it in sheet2 col A & return the value from col B if found