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.

 

[Aladin Akyurek]

Hi Aladin,

Would be in one cell please.

Thank you.

Book1
	A	B	D	E
1	Sales Rep	Product Code	Sales Rep	Product Code
2	Sue	DN099	sue	DN099, DN100, DN101, DN102, DN103, DN104, DN105, DN106, DN107, DN108, DN109, DN110
3	Sue	DN100	tim	DDU09, DDU10, DDU11
4	Sue	DN101
5	Sue	DN102
6	Sue	DN103
7	Sue	DN104
8	Sue	DN105
9	Sue	DN106
10	Sue	DN107
11	Sue	DN108
12	Sue	DN109
13	Sue	DN110
14	Tim	DDU09
15	Tim	DDU10
16	Tim	DDU11
17	Thomas	DN098
18	Adria	UI987
19	Adria	UI988
Sheet1

In D2 control+shift+enter, not just enter:

=TEXTJOIN(", ",1,IF($A$2:$A$19=$D2,$B$2:$B$19,""))

If the native function TEXTJOIN is not available on your system...

In D2 control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF($A$2:$A$19=$D2,", "&$B$2:$B$19,"")),1,2,"")

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[Saba Sabaratnam]

Hi Aberdham,

I just wanted to clarify my Excel setup bit more clear.


Enter your data in range A1 to B19


Enter the following formula in D2 and copy it down

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


Enter the array formula in E2 using Shift + Ctrl + Enter and copy it down.

As you know, there will be curly brackets around the formula when entered correctly.

=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)


Enter Sue in Cell A22 and enter the formula to pick all product codes against her name in one cell.

=INDEX($D$2:$D$19,SUMPRODUCT((MAX(ROW($D$2:$D$19)*(A22=$A$2:$A$19))-1)))

Kind regards

Saba

 

[Aberdham]

Thank you so much both Aladin and Saba,

I decided to apply Aladin's approach since it is one straight formula.

Thanks a lot for your help !!

 

[Aladin Akyurek]

You are welcome. Thanks for the update.