Need to pull data from a column and string it together

[tbednarek]

Hello,

We have a spreadsheet that has information from our PO's. I would like to be able to group all the item numbers into one cell with this symbol | in-between with no spaces. Thank you for your help!

Tony

With spreadsheet below, I would like to know how to put in a formula to create
70187|70170C|70008|70224|70009|70122|70117|70118

Customer Name	Doc. No.	Product #	Qty.	Units	Pounds	Req. Del. Date
A	27119	70187	20	CASE	800	6/1/2017
B	27119	70170C	2	CASE	40	6/1/2017
C	27119	70008	35	CASE	420	6/1/2017
D	27119	70224	5	CASE	60	6/1/2017
E	27119	70009	40	CASE	480	6/1/2017
F	27119	70122	55	CASE	660	6/1/2017
G	27119	70117	80	CASE	960	6/1/2017
H	27119	70118	40	CASE	480	6/1/2017

<colgroup><col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="64" style="width: 48pt;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

 

[hiker95]

tbednarek,

Welcome to the MrExcel forum.

I know that you asked for a formula solution, but, none of the Formula Gurus have responded.

Here is a macro solution for you to consider that will run in the active worksheet.

I assume that your raw data is grouped by Doc. No. in column B .


Sample raw data, and, results, in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H
1	Customer Name	Doc. No.	Product #	Qty.	Units	Pounds	Req. Del. Date
2	A	27119	70187	20	CASE	800	6/1/2017	70187|70170C|70008|70224|70009|70122|70117|70118
3	B	27119	70170C	2	CASE	40	6/1/2017
4	C	27119	70008	35	CASE	420	6/1/2017
5	D	27119	70224	5	CASE	60	6/1/2017
6	E	27119	70009	40	CASE	480	6/1/2017
7	F	27119	70122	55	CASE	660	6/1/2017
8	G	27119	70117	80	CASE	960	6/1/2017
9	H	27119	70118	40	CASE	480	6/1/2017
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub JoinProductNumbers()
' hiker95, 06/02/2017, ME1007986
Dim lr As Long, lc As Long, r As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = 2 To lr
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    If n > 1 Then
      .Cells(r, lc + 1) = Join(Application.Transpose(.Range("C" & r & ":C" & (r + n) - 1)), "|")
    End If
    r = r + n - 1
  Next r
  .Columns(lc + 1).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the JoinProductNumbers macro.

 

[hiker95]

tbednarek,

Here is an updated macro, just in case the information in column B only has one Doc. No..

New sample raw data, and, results, in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H
1	Customer Name	Doc. No.	Product #	Qty.	Units	Pounds	Req. Del. Date
2	A	27119	70187	20	CASE	800	6/1/2017	70187|70170C|70008|70224|70009|70122|70117|70118
3	B	27119	70170C	2	CASE	40	6/1/2017
4	C	27119	70008	35	CASE	420	6/1/2017
5	D	27119	70224	5	CASE	60	6/1/2017
6	E	27119	70009	40	CASE	480	6/1/2017
7	F	27119	70122	55	CASE	660	6/1/2017
8	G	27119	70117	80	CASE	960	6/1/2017
9	H	27119	70118	40	CASE	480	6/1/2017
10		33333	7777777					7777777
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub JoinProductNumbers_V2()
' hiker95, 06/02/2017, ME1007986
Dim lr As Long, lc As Long, r As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = 2 To lr
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    If n = 1 Then
      .Cells(r, lc + 1) = .Range("C" & r)
    ElseIf n > 1 Then
      .Cells(r, lc + 1) = Join(Application.Transpose(.Range("C" & r & ":C" & (r + n) - 1)), "|")
    End If
    r = r + n - 1
  Next r
  .Columns(lc + 1).AutoFit
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my last reply.

Then run the JoinProductNumbers_V2 macro.

 

[tonyyy]

tbednarek,

Welcome to the Board.

With spreadsheet below, I would like to know how to put in a formula to create
70187|70170C|70008|70224|70009|70122|70117|70118

If you're using Excel 2016 (which I'm not), you have access to the function TEXTJOIN:

"The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges."

If you're using a version prior to Excel 2016, you can step through the following process to create a pseudo-array formula:

1. Select cell H1.
2. Type =TRANSPOSE(C2:C9)&"|" in formula bar.
3. Press F9.
4. Delete curly brackets in formula bar.
5. Delete last delimiting character.
6. Type =Concatenate( in front of all characters in formula bar.
7. Type ) after last character in formula bar.
8. Press Enter

Cheers,

tonyyy