0

I have this table: enter image description here

on Cell A4 i want the value "Orange". I want to take only the summary of the three Fruit columns marked in orange:

  • table format (so instead of cells B2,E2,H2 I need [@Fruit 1], [@Fruit 2], [@Fruit 3])
  • only the fruit columns which are separated by irrelevant price and weight columns between them.
  • no duplicates (so Fruit 1: Apple & Fruit 2: Apple 2 becomes just "Apple" and not "Apple,Apple")
  • no blanks (so Row 4 will be "Orange" and not "Orange,")

any help will be greatly appreciated.

Kobe2424
  • 147
  • 7

3 Answers3

2

If you have Excel365 then use below formula.

=TEXTJOIN(", ",TRUE,UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,Table1[@])&"</s></t>","//s[translate(.,'1234567890','')=.]")))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • i do have Excel 365, it seems like Spencer Barnes' answer is more general and it works, i appreciate the help! – Kobe2424 Mar 30 '21 at 09:58
1

You were right to include the textjoin tag because that's what you're going to need to use. Something like this:
=TEXTJOIN(", ", TRUE, [@Fruit 1], IF([@Fruit 1] = [@Fruit 2], "", [@Fruit 2]), IF(OR([@Fruit 1] = [@Fruit 3], [@Fruit 2] = [@Fruit 3]), "", [@Fruit 3])).

That second parameter in the Textjoin formula sets it to ignore empty values. The IF formulas return empty values if their value has shown in a previous column (that is, Fruit 2 checks Fruit 1, Fruit 3 checks both Fruit 1 and 2 columns).

Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26
0

Suppose your table is called Tbl_Fruit, in Cell A2, you can enter the following formula and drag it down:

=TEXTJOIN(", ",,UNIQUE(INDEX(Tbl_Fruit[[Fruit 1]:[Fruit 3]],ROW()-1,{1;4;7})))

Alternatively, you can enter the following formula in A2:

=TEXTJOIN(", ",,UNIQUE(TRANSPOSE(INDEX(FILTER(Tbl_Fruit[[Fruit 1]:[Fruit 3]],LEFT(Tbl_Fruit[[#Headers],[Fruit 1]:[Fruit 3]],5)="Fruit"),ROW()-1))))
Terry W
  • 3,199
  • 2
  • 8
  • 24