Merging formula selected cells into one cell

[IanG]

Hi

I have limited knowledge of Excel formulas, so need assistance.

I have a spreadsheet that has over 5000 rows of data. This data is made up of hundreds of sizes of product as listed in Column B, and with each product having various pack sizes as listed in Column D (number of pack sizes can vary from 10 to 13). The sample spreadsheet is only a snip of two products, and showing the pack sizes.

On the attached spreadsheet I need to;

merge cells D3:D14, into one cell with the data separated by “;”
merge cells F3:F14, into one cell with the data separated by “;”
merge cells D15:D24, into one cell with the data separated by “;”
merge cells F15:F24, into one cell with the data separated by “;”

The above selection is based on the different product in column B (or column E data).

I need a formula that covers the full 5000+ rows (and hundreds of sizes of product) using either column B or E to group each product size and then merge the corresponding data of that product size in columns D and F into one cell with the data separated by “;”

The sample also shows the end result needed in rows 29 and 30.

Thanks

 

[bosco_yip]

Try.........

1] Helper1, "Concatenated Pack Size" H3, formula copy down :

=D3&IFERROR(";"&VLOOKUP(B3,B4:H$27,7,),)

2] Helper2, "Concatenated Custom Label" I3, formula copy down :

=F3&IFERROR(";"&VLOOKUP(B3,B4:I$27,8,),)

3] Range B28:F28, changed output header name as per attached file.

4] Output "Size" B29, formula copy down :

=IFERROR(INDEX(B$3:B$26,MATCH(0,INDEX(COUNTIF(B$28:B28,B$3:B$26&""),),0)),"")

5] Output "Brand" C29, formula copy across to F29 and all copy down :

=VLOOKUP($B29,$B$3:$I$26,MATCH(C$28,$B$2:$I$2,0),0)

Regards
Bosco

 

[IanG]

Hi Bosco

Really appreciate your reply. I don't know how that formula works, as I said I don't have a lot of formula knowledge. To me, it just works!
It has got me out of a lot of work.

Thanks
Ian