0

Good Day Everyone

I have an Excel sheet as bellow in two columns. I know how to use an array if function with sum to add up all values next to a column that has met the criterea as in {=sum(if(A1:A5="YES",B1:B5)} but how to go about doing it with strings and concatenate so that they show the result as below

Thanks for Any Help

The Excel array

Edit: I know there is a easy VBA solution, but was wondering if there is an excell way since there is an easy solution if it was values and not strings.

Nico De Klerk
  • 3
  • 2

3 Answers3

1

If I'm assuming you don't want the relatively easy VBA solution where you recurse through the initial list and concatenate the answers then you could put the following into B8:

=IFERROR(INDEX(Sheet1!$A$1:$A$6,SMALL(IF(Sheet1!$B$1:$B$6=$A8,ROW(Sheet1!$B$1:$B$6)-ROW(Sheet1!$B$1)+1),COLUMNS($B1:B1))),"")

You'd have to drag it across as it'll put them in separate columns but then you should be able to concatenate them into a single column afterwards.

You have to have Ctrl and Shift held down when inputting as it's an array formula.

Tim Edwards
  • 1,031
  • 1
  • 13
  • 34
  • This does not concatenate it though, I have to drag the formula to the right to get a transpose of the answer. Then Concatenate. Very Very Usefull thought Thank you – Nico De Klerk Jul 21 '16 at 14:50
0
  • Activate iteration in the options. The maximum number of iterations must be equal to or greater than the number of lines.
  • Add =IF(D1=100;1;D1+1) to cell D1
  • This is the formula in cell E1 for the Stark houses: =IF(D1=1;"";IF(D1-1>COUNTIF(B:B;"Stark");E1;E1&INDEX(A$1:A$100;SMALL(IF(B$1:B$100="Stark";ROW($1:$100));D1-1))))

(I hope that I got the English names of the functions correctly.)

I missed one information: You must enter the function as an array function.

Thanks go to http://www.excelformeln.de/formeln.html?welcher=155

Michael Westwort
  • 914
  • 12
  • 24
  • For this formula it might be useful not to have any other entries below the raw data. Therefore the results should be in a different column (E in this case). However, I think you can simply replace 100 by the respective number of lines of your raw data. – Michael Westwort Jul 20 '16 at 09:21
  • Sorry for the dumb question but isnt adding =IF(D1=100;1;D1+1) to D1 going to cause a Circular furmula? – Nico De Klerk Jul 21 '16 at 14:48
  • If you have iteration actived, that is fine. – Michael Westwort Jul 21 '16 at 17:31
0

Was really Strugling to get it right and eventualy got an answer from a stackoverflow question on how to concatenate whole columns.

Sorted the data via B and then used =TRANSPOSE(A4:A6)

Then Used F9 on the function to get this line ={"Mansion1","Mansion 2","House 3"}

Copied and Concatenate and just remove the {

But this was not ideal as the data set I want to use this on is 2500+ lines :( Manualy did it though :( :( but will try the above answers for future use.

Nico De Klerk
  • 3
  • 2