CONCAT based on Line Number

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • yoda.png
    yoda.png
    18 KB · Views: 4

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
The easy way would be to use something like this, then filter to show line 1 only.
Cell Formulas
RangeFormula
D2:D14D2=IF(A2=A3,C2&" "&D3,C2)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1MemberlinenotesConcat
2Member X1The quick brown foxThe quick brown fox jumps over the lazy dog is an example of a sentence that contains every letter of the alphabet
3Member X2jumps over the 
4Member X3lazy dog is an 
5Member X4example of a sentence 
6Member X5that contains every 
7Member X6letter of the alphabet 
8Member Y1Hi my name isHi my name is Bobby I like to play baseball in the summer
9Member Y2Bobby I like 
10Member Y3to play baseball 
11Member Y4in the summer 
12Member Z1Old McDonald hadOld McDonald had a farm
13Member Z2a farm 
14
15
Main
Cell Formulas
RangeFormula
D2:D13D2=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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
881
Office Version
  1. 2013
Platform
  1. Windows
Hi
VBA Option
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,676
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top