• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get data from column A and B based on the value in column c

Ragi

New Member
Hi,

I need an help here.

I have been trying to work on a macro. A,B, and C are the three column which contains MFR, MPN and ID Number.

In sheet 1 if value of column c matches with values of column B in sheet 2, then expected result sheet should get the ID number which matched with sheet 2 along with its MPN and MFR in sheet 1.

The problem is that the Id numbers will contain MFR and MPN at the first row and other remaining rows would be empty.

So please help to get the result with the VBA Macro.

Thanks,
Ragi
 
Hi,

I have tried to upload but it is not getting uploaded.

Is there any other way that i can share my sample file?

BR,
Ragi
 
Hi,

I have tried and same error occurs :(

Can i have any mail address so that i can mail the sample file?

BR,
Ragi
 
Yahoo...Finally i made it is a browser issue.

Please find the attached sample file.
 

Attachments

  • Sample file.xls
    39 KB · Views: 7
Hi,

Check out the attached file.

A macro has been created to achieve this
 

Attachments

  • Sample file.xls
    49 KB · Views: 8
Hello Ragi,

Here is one way. Firstly assuming SN# in Sheet1 will be a VALID number, MFR & MPN will be always text.

In a new sheet

D2

=INDEX(Sheet1!D:D,SMALL(IF(ISNUMBER(MATCH(Sheet1!D$2:D$56,Sheet2!B:B,0)),ROW(Sheet1!D$2:D$56)),ROWS(D$2:D2)))

C2,

=LOOKUP("zzzzz",Sheet1!C$1:INDEX(Sheet1!C:C,SMALL(IF(Sheet1!D$2:D$56=D2,ROW(Sheet1!D$2:D$56)),COUNTIF(D$2:D2,D2))))

B2,

=LOOKUP("zzzzz",Sheet1!B$1:INDEX(Sheet1!B:B,SMALL(IF(Sheet1!D$2:D$56=D2,ROW(Sheet1!D$2:D$56)),COUNTIF(D$2:D2,D2))))

A2,

=LOOKUP(9.9E+300,Sheet1!A$1:INDEX(Sheet1!A:A,SMALL(IF(Sheet1!D$2:D$56=D2,ROW(Sheet1!D$2:D$56)),COUNTIF(D$2:D2,D2))))

All are Array Formulas. So must be confirmed with CTRL+SHIFT+ENTER

See the attached.
 

Attachments

  • Sample file - Hasi.xls
    41 KB · Views: 5
Thank you Sathish..This working great.

But can you please explain me the logic behind this formula?

Thanks in advance:)
 
Hi,
Please see the check it. Very easy to understand the code logic and execution.
 

Attachments

  • Sample file.xlsm
    18.5 KB · Views: 5
Back
Top