CONCAT based on Line Number

[Sphinx404]

I'm trying to figure out how to make my AS400 system more readable after pulling it into Excel.

I have multiple lines for a single entry. The multiple lines come from the "Notes". But luckily it labels the line number.


Columns: Member, Line, Notes

Member X - Line 1 - The quick brown fox
Member X - Line 2 - jumps over the
Member X - Line 3 - lazy dog is an
Member X - Line 4 - example of a sentence
Member X - Line 5 - that contains every
Member X - Line 6 - letter of the alphabet

Member Y - Line 1 - Hi my name is
Member Y - Line 2 - Bobby I like
Member Y - Line 3 - to play baseball
Member Y - Line 4 - in the summer
Member Z - Line 1 - Old McDonald had
Member Z - Line 2 - a farm


Single Line Output =
Member X - The quick brown fox jumps over the lazy dog is an example of a sentence that contains every letter of the alphabet
Member Y - Hi my name is Bobby I like to play baseball in the summer
Member Z - Old McDonald had a farm

I've tried some logic based on the line number, but I can't get it to tell when the line procession is complete. 2 comes after 1, 3 comes after 2, 4 comes after 3, and if not in this procession, then restart.

Any help would be greatly appreciated.

 

[jasonb75]

The easy way would be to use something like this, then filter to show line 1 only.

Book1
	A	B	C	D
1	Member	line	notes	Concat
2	Member X	1	The quick brown fox	The quick brown fox jumps over the lazy dog is an example of a sentence that contains every letter of the alphabet
3	Member X	2	jumps over the	jumps over the lazy dog is an example of a sentence that contains every letter of the alphabet
4	Member X	3	lazy dog is an	lazy dog is an example of a sentence that contains every letter of the alphabet
5	Member X	4	example of a sentence	example of a sentence that contains every letter of the alphabet
6	Member X	5	that contains every	that contains every letter of the alphabet
7	Member X	6	letter of the alphabet	letter of the alphabet
8				0
9	Member Y	1	Hi my name is	Hi my name is Bobby I like to play baseball in the summer
10	Member Y	2	Bobby I like	Bobby I like to play baseball in the summer
11	Member Y	3	to play baseball	to play baseball in the summer
12	Member Y	4	in the summer	in the summer
13	Member Z	1	Old McDonald had	Old McDonald had a farm
14	Member Z	2	a farm	a farm
Sheet4

Cell Formulas
Range	Formula
D2:D14	D2	=IF(A2=A3,C2&" "&D3,C2)

 

[Fluff]

What version of Excel are you using? Please update your account details to show this, as it affects which functions you can use.

If you have textjoin, you could use

+Fluff New.xlsm
	A	B	C	D
1	Member	line	notes	Concat
2	Member X	1	The quick brown fox	The quick brown fox jumps over the lazy dog is an example of a sentence that contains every letter of the alphabet
3	Member X	2	jumps over the
4	Member X	3	lazy dog is an
5	Member X	4	example of a sentence
6	Member X	5	that contains every
7	Member X	6	letter of the alphabet
8	Member Y	1	Hi my name is	Hi my name is Bobby I like to play baseball in the summer
9	Member Y	2	Bobby I like
10	Member Y	3	to play baseball
11	Member Y	4	in the summer
12	Member Z	1	Old McDonald had	Old McDonald had a farm
13	Member Z	2	a farm
14
15
Main

Cell Formulas
Range	Formula
D2:D13	D2	=IF(COUNTIFS(A$2:A2,A2)=1,TEXTJOIN(" ",,IF($A$2:$A$20=A2,$C$2:$C$20,"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sphinx404]

@Fluff @jasonb75

Both versions worked. Thank you both so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mohadin]

Hi
VBA Option

Sub test()
    Dim a As Variant, i
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), a(i, 3)
                Else
                    .Item(a(i, 1)) = .Item(a(i, 1)) & "" & a(i, 3)
                End If
            End If
        Next
       Cells(1, 1).Offset(, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    End With
End Sub