Match 2 values from one sheet in another sheet.

[Liquorice]

I need some help here.

I have 2 worksheets.
in the first sheet I have 500 lines of data. 2 of the columns contain a number which create a unique identifier together.

In the second sheet have 8000 lines of data and need to be able to extract the 200 lines I need from it.

This by searching by the 2 identifiers in sheet 1. how can I solve this without having to do it manually?

[Carim]

Hi,

For a start, take a look at Data Filter Automatic

and Data Filter Advanced Filter ...


HTH
Carim

[Liquorice]

Hi,

For a start, take a look at Data Filter Automatic

and Data Filter Advanced Filter ...


HTH
Carim

Hi

thanks for the tip but I checked this earlier but didn`t get it do to what I wanted to.

I need to use the data in the 2 columns in sheet one as a criteria for the search in sheet2.

[geraintathomas]

If I'm understanding you, you could try to CONCATENATE the two unique ID's, then Vlookup the data page, then autofilter the vlookup.

[Liquorice]

If I'm understanding you, you could try to CONCATENATE the two unique ID's, then Vlookup the data page, then autofilter the vlookup.

Hmmmm, would you be able to give me a description on how to do this as I`m quite new to excel.

[Carim]

Hi,

Best solution for a tailor-made answer, is to upload a zipped copy of your worksheet ...

HTH
Carim

[Liquorice]

Hi

Here are a cut down version of the sheets.

as you can see the values in sheet 1 "column A and Column C" match sheet 2 "column E and Column I".

I need to check the values in all of the cells in sheet 1 column A and C, up against The cells in Column E and I in sheet 2.

then export the whole row in sheet 2 together with the values in sheet 1.

[Carim]

Hi,

For a process to be automated, data consistency is crucial ...
there are a couple of cases which, whatever the solution, will cause difficulties
1. Customer PO = 234316 + 234313
2. Customer PO = 234566ORIGINAL
3. Item No = RECOVERY

How should these data be handled ?

HTH
Carim

[Liquorice]

those can just be skipped, you can make a suggestion for me based on a list with data consistency. I`ll just make sure to clean the list before I start.

[Carim]

Attached is your worksheet with comparisons, as a starting point ...

HTH
Carim

[Liquorice]

Great

Is it possible to get all the cells in the lines where the numbers match in sheet2 printed in this sheet?

or is there a way to choose which cells I want the data printed from?

[Carim]

Ok...
just added a filter for you ...

HTH
Carim

[Liquorice]

I think I`ll need an explanation on how to use this when it`s done. will I just be able to copy sheet 1 and sheet 2 into the thing you made?

[Carim]

I have not done a lot ...
just one conditional format
and one filter on the conditional format ...

Yes you can go ahead and copy all your data in the two sheets, it will work exactly in the same way ...

HTH
Carim

[Liquorice]

I have not done a lot ...
just one conditional format
and one filter on the conditional format ...

Yes you can go ahead and copy all your data in the two sheets, it will work exactly in the same way ...

HTH
Carim

Great stuff, I would be REALLY happy if you could solve my headache

[Carim]

Glad your problem is fixed.

Thanks for the feedback

Carim

[Liquorice]

Glad your problem is fixed.

Thanks for the feedback

Carim

Hi

Sorry but i dind`t mean that my problem was fixed but I ment that I would be really happy if you could help me out so my problem WOULD be fixed

What you did so far was really on the right track, I just need more info from sheet 2 in the report.

[Carim]

Hi,

No problem ... But can I guess what you mean by

more info from sheet 2

...

Carim

[Liquorice]

Hi,

No problem ... But can I guess what you mean by
...

Carim

I`løl try to explain here.

as I could see you were able to find the 2 numbers from the columns in sheet 1 on sheet 2. The thing I need is the data from the whole row where you find the numbers in sheet 2.

f.ex lets say you have two numbers in row 1 sheet 1, these numbers are found in row 6 sheet 2. what I need to be able to do then is to get the all the cells in row 6 together with the 2 numbers we have used as a search kriteria.

[Carim]

Hi,

Attached is a second filter in sheet2 for (hopefully ) your last worksheet ...

HTH
Carim

[Liquorice]

Could you just tell me how I should use this? Where should I import the data from sheet 1 and where should I import the data from sheet 2? What do I do after the data has been imported?

another question. Is there any way we can get the data from the row instead of the row number.?
I need to know which row in sheet 2 the data is located, not the other way around if you know what I mean.

[Carim]

Once the match function provides with the row, just combine with index function() to get the equivalent of vlookup() function ...

=index(wholerange,yourmatch,columNumber)

HTH
Carim

[Liquorice]

Once the match function provides with the row, just combine with index function() to get the equivalent of vlookup() function ...

=index(wholerange,yourmatch,columNumber)

HTH
Carim

Could you please do this for me in the worksheet? I`m a real noob when it comes to excel so I`ll probably do something wrong if I try to do this.

[Carim]

HTH
Carim

[Liquorice]

we are almost there now

instead of col 1 in sheet 1. is it possible to get the info from sheet2 instead. and instead of col1, is it possible to get all the columns in the row the criteria is met?