I have a code that concatenates together information from bunch of columns. I need concatenate all this information together
as there is a another report where I need to do match and index ( or simple vlookup) and have a nice overview to see where we
are with a deliveries. Concatenation results should go to column X. This is what I have so far:
Dim a, i As Long: Application.ScreenUpdating = False
With Sheets("before"): a = .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp).Offset(, 22)): End With
For i = 1 To UBound(a)
a(i, 1) = "PO " & a(i, 8) & "; Item " & a(i, 19) & "; Part " & a(i, 4)
& "; Latest delivery date and time " & Format(a(i, 14), "dd/mm/yyyy") & ", " &
a(i, 10) & "; Manifests " & a(i, 1) & "; Delivery notes " & a(i, 2) & "; Total
quantity x" & a(i, 5):
Next: Sheets("before").Cells(Rows.Count, "Y").End(xlUp).Offset(1, -1).Resize(UBound(a), 1) = a
Columns(14).NumberFormat = "dd/mm/yyyy"
Columns(23).NumberFormat = "0"
This code combines some data together in a way that after I do a vlookup to another report I can read and understand whats
going on. So the combined information would look like this example:
PO 1800786639; Item 10; Part L57952013200; Latest delivery date and time 04/01/2011, 11.30AM TRANSPORT; Manifests 10072;
Delivery notes 81630937; Total quantity x1
Now there are some rows that as per column W are duplicates. I need to
combine these lines together into a one row, delete all duplicates and only keep the bottom line. This is where I
For the first 5 parameters (PO, Item, Part, Last delivery date and time) I would like the macro to keep
the values of the bottom duplicate. With Manifest ID I would like the macro to combine together all Manifest ID-s that are
unique. With Delivery notes it should combine all the delivery notes together. Theoretically (and in practically) there will
always be only unique delivery notes ( haven't seen singe duplicate delivery note so far). With Total quantity it should sum
up the duplicate lines i.e there are are three rows with qyantites 4, 2, 3 so the total sum would be x9.
line where rows have been added together:
PO 1800824763; Item 10; Part KNA12A3M30701A04; Latest delivery date and time 20/02/2011, 11.30AM TRANSPORT; Manifests 11232,
11250; Delivery notes 81649755, 81649754, 81649753, 81649750, 81649749, 81649746, 81649744; Total quantity x7
have attached a xls where there are sheets "before" and "after" along with some decent comments. All the
duplicate lines as per column W have been highlighted in red. After you have you run the macro, then sheet "before"
should look identical with sheet "after".
Any help would be greatly appreciated.