Solved

Excel formula lookup multiple charges

Posted on 2016-08-17
11
68 Views
Last Modified: 2016-08-17
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
0
Comment
Question by:Euro5
  • 6
  • 4
11 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41759356
the question is not clear to me,  what the end result should look like? is it shown in the sheet called "Sample of final"?
0
 

Author Comment

by:Euro5
ID: 41759426
Sorry, yes, the final should look like that. The information is compiled from the two other sheets.
I need a formula in the shipments sheet to pull the charges into it.
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41759432
Please try this....

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

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
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
If slr < 2 Or clr < 2 Then
   MsgBox "Data not found!", vbCritical
   Exit Sub
End If
If slc > 10 Then sws.Range("K1", sws.Cells(slr, slc)).Clear
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)), ";")
   sws.Range("K" & i + 1).Resize(1, UBound(z, 1) + 1).Value = z
Next i

slc = sws.UsedRange.Columns.Count
sws.Range("A" & slr + 1).Copy
sws.Range("K2", sws.Cells(slr, slc)).PasteSpecial operation:=xlAdd
sws.Range("K2", sws.Cells(slr, slc)).NumberFormat = "#0.00"
sws.Range("K1:L1").Value = Array("Surcharge desc 1", "Surcharge Amount 1")
sws.Range("K1:L1").AutoFill sws.Range("K1", sws.Cells(1, slc)), xlFillDefault
sws.Columns.AutoFit
Set dict = Nothing
End Sub

Open in new window

EXAMPLE-LOOKUP.xlsm
0
 

Author Closing Comment

by:Euro5
ID: 41759462
Excellent - thanks so much!!
0
 

Author Comment

by:Euro5
ID: 41759470
Subodh Tiwari (Neeraj), if there are no charges associated with the shipping, I get an error on this line. Can we just have it move on if there are no associated charges?

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

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Euro5
ID: 41759477
ALSO, I always starts on column K. I don't know how many columns will be in the shipping report. Can it start the first empty column?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759491
Replace existing line#34 with the following....

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

Open in new window


See if that resolves your issue.
0
 

Author Comment

by:Euro5
ID: 41759516
Yes - that eliminated the error - thanks!!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759528
Replace the previous code with the following one.
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

Open in new window

0
 

Author Comment

by:Euro5
ID: 41759552
PERFECT!  Thanks so much!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759556
You're welcome.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now