This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I have two spread sheets, one with shipments and another with the charges associated with the shipments.

There are multiple charges to each shipment.

I need to combine - pulling the multiple charges into the same row as the shipment.

Challenges - I don't know how many shipments there will be or how many charges - or how many charges for each shipment.

I attached a sample, in case that helps.

EXAMPLE-LOOKUP.xlsx

There are multiple charges to each shipment.

I need to combine - pulling the multiple charges into the same row as the shipment.

Challenges - I don't know how many shipments there will be or how many charges - or how many charges for each shipment.

I attached a sample, in case that helps.

EXAMPLE-LOOKUP.xlsx

I need a formula in the shipments sheet to pull the charges into it.

```
sws.Range("K" & i + 1).Resize(1, UBound(z, 1) + 1).Value = z
```

```
If UBound(z, 1) >= 0 Then
sws.Range("K" & i + 1).Resize(1, UBound(z, 1) + 1).Value = z
End If
```

See if that resolves your issue.

The tweaked code will place the data dynamically on Shipments sheet.

```
Sub CombineShipmentCharges()
Dim sws As Worksheet, cws As Worksheet
Dim x, y, z, dict
Dim i As Long, j As Long, slr As Long, clr As Long, slc As Long, nslc As Long
Dim str
Set sws = Sheets("Shipments")
Set cws = Sheets("Charges")
slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
clr = cws.Cells(Rows.Count, 1).End(xlUp).Row
slc = sws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
If slr < 2 Or clr < 2 Then
MsgBox "Data not found!", vbCritical
Exit Sub
End If
Set dict = CreateObject("Scripting.Dictionary")
x = sws.Range("A2:A" & slr).Value
y = cws.Range("A1").CurrentRegion.Value
For i = 1 To UBound(x, 1)
dict.Item(x(i, 1)) = ""
Next i
For i = 2 To UBound(y, 1)
If dict.Item(y(i, 1)) = "" Then
dict.Item(y(i, 1)) = y(i, 10) & ";" & y(i, 11)
Else
dict.Item(y(i, 1)) = dict.Item(y(i, 1)) & ";" & y(i, 10) & ";" & y(i, 11)
End If
Next i
For i = 1 To UBound(x, 1)
z = Split(dict.Item(x(i, 1)), ";")
If UBound(z, 1) >= 0 Then
sws.Cells(i + 1, slc).Resize(1, UBound(z, 1) + 1).Value = z
End If
Next i
nslc = sws.UsedRange.Columns.Count
sws.Range("A" & slr + 1).Copy
sws.Range(sws.Cells(2, slc), sws.Cells(slr, nslc)).PasteSpecial operation:=xlAdd
sws.Range(sws.Cells(2, slc), sws.Cells(slr, nslc)).NumberFormat = "#0.00"
sws.Range(sws.Cells(1, slc), sws.Cells(1, nslc)).Value = Array("Surcharge desc 1", "Surcharge Amount 1")
sws.Range(sws.Cells(1, slc), sws.Cells(1, slc + 1)).AutoFill sws.Range(sws.Cells(1, slc), sws.Cells(1, nslc)), xlFillDefault
sws.Columns.AutoFit
Set dict = Nothing
End Sub
```

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

In the attached, on Shipments Sheet, click the button "Combine Data" to get the desired output.

Open in new window

EXAMPLE-LOOKUP.xlsm