Formula Help. (One condition transfer all)

[Aberdham]

Hey all,

I know it is possible to use index and aggregate to extract data row by row. But I would like to have all the data in 1 row. but got stuck there, Perhaps transpose?
[TABLE="width: 174"]
<!--StartFragment--> <colgroup><col width="87" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"]Sales Rep[/TD]
[TD="width: 87"]Product Code[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN099[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN100[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN101[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN102[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN103[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN104[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN105[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN106[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN107[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN108[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN109[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN110[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]DDU09[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]DDU10[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]DDU11[/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]DN098[/TD]
[/TR]
[TR]
[TD]Adria[/TD]
[TD]UI987[/TD]
[/TR]
[TR]
[TD]Adria[/TD]
[TD]UI988[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

Here is how I would like to have
[TABLE="width: 684"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sales Rep[/TD]
[TD]Product Code[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]DN099;DN100;DN101;DN102;DN103;DN104;DN105;DN106;DN107;DN108;DN109;DN110[/TD]
[/TR]
[TR]
[TD]Tim [/TD]
[TD]DDU09;DDU10;DDU11[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in adv.

 

[Worf]

Do you have Office 365? If so, an array formula with the TEXTJOIN function can be used.

 

[Aberdham]

Hi Worf,

Thanks for your reply. Currently I am using excel 2010

 

[Saba Sabaratnam]

I used intermediate formulas to get your result

Enter the following formula into C2

[TABLE="width: 173"]
<tbody>[TR]
[TD="width: 173"]=COUNTIFS($A$2:A2,A2)[/TD]
[/TR]
</tbody>[/TABLE]

Enter the following array formula into D2 using Shift + Control + Enter

[TABLE="width: 1088"]
<tbody>[TR]
[TD="width: 1088"]=IF(C2=1,INDEX($B$2:$B$19,MATCH(A2,$A$2:$A$19,0)),INDEX($D1:D$1,MATCH(1,((C2-1)=$C$1:C1)*(A2=$A$1:A1),0))&";"&B2)

Copy them down

Enter the following formula against Sue

[TABLE="width: 523"]
<tbody>[TR]
[TD="width: 523"]=INDEX($D$2:$D$19,SUMPRODUCT((MAX(ROW($D$2:$D$19)*(A22=$A$2:$A$19))-1)))

Copy them down.

I have the solution workbook and am not allowed to post it here.

Kind regards

Saba[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Worf]

I have the solution workbook and am not allowed to post it here. ​

To share a file, upload it to a hosting site and paste the link here.

 

[Aladin Akyurek]

Is this

DN099;DN100;DN101;DN102;DN103;DN104;DN105;DN106;DN107;DN108;DN109;DN110

in one cell or multiple cells?

 

[Saba Sabaratnam]

it is in one cell and is produced by =INDEX($D$2:$D$19,SUMPRODUCT((MAX(ROW($D$2:$D$19)*(A22=$A$2:$A$19))-1)))


you can download the file from the link below.

https://sabercathost.com/5dq2/Solution.xlsx

Thank you Worf

Kind regards

Saba

 

[Aberdham]

Hi Saba,

Thank you so much for your post. I am getting some error by testing the formula. I will try it out later today on my original spreadsheet and will post back.

 

[Aladin Akyurek]

…and answer post #6 , I hope.

 

[Aberdham]

Hi Aladin,

Would be in one cell please.

Thank you.