To list down all SN (Serial Number)

[Andri]

Dear All,

Please help to solve it by FORMULA.

I have a database e.g 20 Rows, the fields Product Name, SN, etc (Sheet A)

I would like to concatenate the SN, if match with the product Name (Sheet B).
For Example : if Col A, Product Name = Computer, Col B, i want to be text
the SN like this (A1234, B1234, C1234) from the database in Sheet A.

thank you for your kind attention and help.

TIA and respectfully,
andri

 

[muddan madhu]

Try

In sheet A put this formula in B2 =VLOOKUP(A2,Sheet2!$A$1:$B$20,2,0)
and drag it.

 

[Andri]

Dear Muddan,

thank you for the response. but that Vlookup formula only list one of SN
(the first occurance).

But i would like to list down all the SN if the Product Name is match.
SN = TEXT data.

Thank you and looking forward further guidance.

respectfully,
andry

 

[muddan madhu]

Use this formula in sheet A

=INDEX(SheetB!$B$1:$B$10,SMALL(IF(SheetB!$A$1:$A$10=sheetA!$A
$2,ROW(SheetB!$A$1:$A$10)),ROW(1:1)),1)

use ctrl + shift + enter this is an array formula

 

[Andri]

Dear Madhu,

Still cannot get what i wish to do.

Here are the details:
Sheet1

Col A Col B (SN)
Printer A1234
Computer XXXX1
Printer B1234
Keyboard BBBBB
Computer XXXX2
Printer C1234

Sheet2, i wish to do like this

Col A COL B (the cell i have to enter the formula)
Printer A1234, B1234, C1234
Computer XXXX1,XXXX2
Keyboard BBBBB

Please help and TIA.

Respectfully,
andry

 

[muddan madhu]

I am getting the results.....

sheet 1 u have data
sheet 2 u need output (result)

go to sheet 2 , cell A1 u have Printer, Cell A2 u have computer,
cell A3 u have keyboard, in cell B1 put this formula
=INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!A1,ROW(Sheet1!
$A$1:$A$6)),ROW($1:$1)),1)
once u enter the formula not just an enter but use Ctrl + Shift +
Enter and drag the formula till B3.

u will see the result as
A1234
XXXX1
BBBBB

and C1 put this formula
=INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!A1,ROW(Sheet1!
$A$1:$A$6)),ROW($2:$2)),1)
once u enter the formula hit ctrl +shift + enter and drag it till C3.

u will see the result as
B1234
XXXX2
#NUM!

If u want to see blank cell instead of error in the cell then use
=IF(ISERROR(INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!
A3,ROW(Sheet1!$A$1:$A$6)),ROW($2:$2)),1)),"",INDEX(Sheet1!$B$1:$B
$6,SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!A3,ROW(Sheet1!$A$1:$A
$6)),ROW($2:$2)),1))

similarly for D1 ....

finally u will see the results as
A1234 B1234 C1234
XXX1 XXX2
BBBB

In B2 just add &","&C2&","&D2
similarly for C2 and D2.

u will get it as
A1234,B1234,C1234..
..
..
..

 

[Andri]

Dear Muddan,

Thank you for your excellent formula...
can we edit the formula, to add how many the SAME SN being sold?

6A1234 2B1234 4C1234
3XXX1 1XXX2
4BBBB

6A1234 shows that there are 6 same SN for A1234 (the first digit represent
the frequency).

TQ and respectfully,
andry