Previous Topic Next Topic

Excel Macro: combine selected rows

Solved/Closed
Steve - Oct 21, 2011 at 06:12 AM
iietntech Posts 2 Registration date Thursday December 5, 2013 Status Member Last seen December 6, 2013 - Dec 5, 2013 at 06:30 PM
Hello,

I have source data in Excel looking like this:

CustomerID SalesMonth SalesAmount
12345 Jan 150
12345 Mar 100
12345 Apr 200
56789 Jan 800
56789 May 10
78945 Nov 20
44444 Jan 1000
....

The combination of CustomerID and SalesMonth is unique.

And I would like to combine the source data in a different Excel tab in a form like this:


CustomerID 1stMonth 1stAmount 2ndMonth 2ndAmount 3rdMonth 3rdAmount
12345 Jan 150 Mar 100 Apr 200
56789 Jan 800 May 10
78945 Nov 20
44444 Jan 1000


In total there can be up to 10 different months for a customer (some have only 1 or 2). Important is that there are no 'gaps' in any of the output rows, eg if a customer has sales only in Nov, then the '1stMonth' cell should read 'Nov' for that customer.

Any help on this would be highly appreciated.
Related:

4 responses

Answer 1 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 21, 2011 at 07:39 AM
try this macro and see sheet 2(copy data in sheet 1 to sheet 3 as safety precaution) 

Sub test()
Dim customer As Range, ddata() As Range, custunq As Range, cunq As Range, filt As Range
Dim dest As Range, j As Long, k As Long
With Worksheets("sheet1")
Set customer = Range(.Range("A1"), .Range("A1").End(xlDown))
Set custunq = .Range("A1").End(xlDown).Offset(5, 0)
customer.AdvancedFilter xlFilterCopy, , custunq, True
Set custunq = Range(custunq.Offset(1, 0), custunq.End(xlDown))
For Each custunq In custunq
.Range("A1").CurrentRegion.AutoFilter field:=1, Criteria1:=custunq.Value
Set filt = .Range("A1").CurrentRegion.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count). _
    SpecialCells(xlCellTypeVisible)
  j = WorksheetFunction.CountA(filt.Columns(1))
  'MsgBox j
  ReDim ddata(1 To j)
  With Worksheets("sheet2")
  Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = filt(1, 1)
End With
  For k = 1 To j
   Set ddata(k) = .Range(filt(k, 2), filt(k, 3))
   ddata(k).Copy
   With Worksheets("sheet2")
.Cells(dest.Row, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial
End With
Next k
.Range("A1").CurrentRegion.AutoFilter
Next custunq
Range(.Range("a1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A").End(xlUp)).EntireRow.Delete

End With
End Sub



Sub undo()
Worksheets("sheet2").Cells.Clear
End Sub
1
Steve
Oct 21, 2011 at 11:39 AM
Hello venkat1926,
Thanks so much - this does exactly what I needed!
0
iietntech Posts 2 Registration date Thursday December 5, 2013 Status Member Last seen December 6, 2013
Dec 5, 2013 at 06:30 PM
How can I alter this macro to display the entire row instead of 2 fields?
0
Answer 2 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 21, 2011 at 06:53 AM
You would need a macro for that. The approach in macro would depend on number of customers that one can encounter
0
Answer 3 / 4
Steve
Oct 25, 2011 at 09:42 AM
Hello,

The solution proposed works perfectly - but I cant adjust it to contain also ProductIDs.... how could this be done?
One option would be to merge CutsomerID and ProductID in the source data, run the macro and then separate source CustomerID and ProductID again (customerID has each time 8 digits, ProductID each time 4
Source data:
CustomerID ProductID SalesMonth SalesAmount
12345 A Jan 150
12345 A Mar 100
12345 A Apr 200
12345 B Mar 150
12345 B Apr 100
12345 B Jul 200
56789 B Jan 800
56789 B May 10
78945 A Nov 20
44444 C Jan 1000
....
The combination of CustomerID, Product ID and SalesMonth is unique.
And I would like to combine the source data in a different Excel tab in a form like this:
CustomerID ProductID 1stMonth 1stAmount 2ndMonth 2ndAmount 3rdMonth 3rdAmount
12345 A Jan 150 Mar 100 Apr 200
12345 B Mar 150 Apr 100 Jul 200
56789 B Jan 800 May 10
78945 A Nov 20
44444 C Jan 1000


Appreciate your help again,

Many thanks
0
Answer 4 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 25, 2011 at 08:48 PM
IN column E concatenate the four columns or even whatever column you want and then run macro with one modification in this statement

Set customer = Range(.Range("A1"), .Range("A1").End(xlDown))

change A1 to E1(2 places )

once macro is run successfully you can even delete the column E

see whether any other modification is required.
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses