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?

Sales Rep	Product Code
Sue	DN099
Sue	DN100
Sue	DN101
Sue	DN102
Sue	DN103
Sue	DN104
Sue	DN105
Sue	DN106
Sue	DN107
Sue	DN108
Sue	DN109
Sue	DN110
Tim	DDU09
Tim	DDU10
Tim	DDU11
Thomas	DN098
Adria	UI987
Adria	UI988

<!--StartFragment--> <colgroup><col width="87" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Here is how I would like to have

Sales Rep	Product Code
Sue	DN099;DN100;DN101;DN102;DN103;DN104;DN105;DN106;DN107;DN108;DN109;DN110
Tim	DDU09;DDU10;DDU11

<colgroup><col><col></colgroup><tbody>
</tbody>

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

=COUNTIFS($A$2:A2,A2)

<tbody>
</tbody>

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

=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 =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 <tbody> </tbody>

<tbody>
</tbody>

 

[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.