0

I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process. I have columns as listed in the below screen shot and I want to get my result as in "Expected Result" column. Expected Result

What are all the helper columns I need and how to get my expected values by formula?

Improve this question

2 Answers 2

Reset to default
0

If you have the latest version of Excel, you can try and adapt the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",TRUE,FILTER(FILTER($A$1:$F$14,$A$1:$A$14=A1),{0,0,0,1,1,1})),"RAM","RAM:")," ROM",", ROM:")," Screen size",", Screen size:")," Thickness",", Thickness:")," Certification", ", Certification:")

Drag the formula down to fill the other cells.

Explanation:

  • FILTER($A$1:$F$14,$A$1:$A$14=A1): filters the data based on the first column value (Material number).
  • FILTER(FILTER($A$1:$F$14,$A$1:$A$14=A1),{0,0,0,1,1,1})): the result of the inner/previous formula is filtered to return only the attributes' names, values and units of measurement.
  • TEXTJOIN(" ",TRUE,FILTER(FILTER($A$1:$F$14,$A$1:$A$14=A1),{0,0,0,1,1,1})): the previous formula's results are joined by a space.
  • Finally, some words are replaced to added commas and semi-colons.

Improve this answer
2
  • HI Reddy Lutonadio, Thanks for the reply. But your result is not as expected result. And I want the CERTIFICATION attribute name to come once only. Also it is not always that the CERTIFICATION attribute will appear multiple time, it is possible some other attribute also (ex: THICKNESS) can also come multiple type, so dynamically how to get the expected result so that it will sense whatever attribute appear multiple time within one material number?
    – Vignesh K
    Commented May 8 at 13:39
  • you can refer expected result example in this attached Excel file for better understanding link: docs.google.com/spreadsheets/d/…
    – Vignesh K
    Commented May 8 at 14:58
0

The same idea, but a slightly different formula:

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN({": "," ",", "},TRUE,
FILTER($D$2:$F$22,$A$2:$A$22=$A2))," CERTIFICATION", "",2),
": CERTIFICATION",",")

List

Improve this answer
2
  • Hi @MGonet. Thanks for your reply. In this it is not always that the CERTIFICATION attribute will appear multiple time, it is possible some other attribute also (ex: THICKNESS) can also come multiple type, so dynamically how to get the expected result so that it will sense whatever attribute appear multiple time within one material number?
    – Vignesh K
    Commented May 8 at 12:43
  • you can refer expected result example in this attached Excel file for better understanding link: docs.google.com/spreadsheets/d/…
    – Vignesh K
    Commented May 8 at 14:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .