Macro to group data

[natheplas]

Hello,

I have a list of product codes, descriptions and locations.

I require a macro based on the highlighted list to create 4 columns with headers please.

Product Code, Decription, Loc, Count, then the data, then a space and then the header again, with data etc etc.

The data is grouped by the second number and letter. for example:

QH180300WTHS	180A AIR COOLED MIG TORCH 3MTR	2B1A
QH400400LX	500A WTR COOLED MIG TORCH 4MTR	2B1A
ESF5041	H/DUTY PROTECTO BLANKET 2 X 2MTR	2B1A
RW25-25	T/P W/C 200A 3V	2B1A
EAD006	FINE HIDE YELLOW TIG GLOVES	2B1A
QBD5210BE	EZG/GUNHCA 10X060 EURO	2B1B
QH250401WBLK	CONICAL NOZZLE 250A (PK 25)	2B1B
EW405CE	RED TWIST GRIP 400AMP	2B1B
ET2001	C11 VICE GRIP CLAMP	2B1B
EW600TCE	YELLOW TWIST GRIP 600AMP	2B1B
ELC04305B	4.0MM x 305MM GOUG CARBON [x100]	2B1B

The above would become:

Product Code	Description	LOC	COUNT
QH180300WTHS	180A AIR COOLED MIG TORCH 3MTR	2B1A
QH400400LX	500A WTR COOLED MIG TORCH 4MTR	2B1A
ESF5041	H/DUTY PROTECTO BLANKET 2 X 2MTR	2B1A
RW25-25	T/P W/C 200A 3V	2B1A
EAD006	FINE HIDE YELLOW TIG GLOVES	2B1A
Product Code	Description	LOC	COUNT
QBD5210BE	EZG/GUNHCA 10X060 EURO	2B1B
QH250401WBLK	CONICAL NOZZLE 250A (PK 25)	2B1B
EW405CE	RED TWIST GRIP 400AMP	2B1B
ET2001	C11 VICE GRIP CLAMP	2B1B
EW600TCE	YELLOW TWIST GRIP 600AMP	2B1B
ELC04305B	4.0MM x 305MM GOUG CARBON [x100]	2B1B

Is it possible for a macro to do this based on my highlighted list, as appose to me continuing to copy and based into a worksheet please?

Thank you so much if someone is smart and kind enough to help!

 

[natheplas]

(Please message me if any of the below is unclear, could really do with some help on this one).

 

[diddi]

you could try using the subtotal tool in the Data menu.
it produces close to what you want. then copy / paste values and you have a "flat" copy of all the data with no formulae involved.

 

[johnnyL]

@natheplas I am not sure what you mean by 'highlighted list". That being said, let's see if we can rephrase your desires so members here can assist you better ...

Step 1) You currently have 3 columns of data in your excel sheet
Step 2) You would like to add header titles to each of those 3 columns of data, as well as providing a new, 4th, column header title for the 'Count'
Step 3) You would like to display the data for the 3 column results below that until the value stored in the 3rd column changes
Step 4) You would like to insert a blank row below the 3rd column each time the value stored in the 3rd column changes
Step 5) In addition to this inserted blank row, you would also like to add another header row for the 4 columns below the inserted blank row, similar to Step 2 above
Step 6) You would like to repeat steps 3 - 5 until all Column 3 values have been processed

That sound right? Also, the 4th column you want added ... 'Count' ... are you purposely leaving those cells blank?

 

[natheplas]

@natheplas I am not sure what you mean by 'highlighted list". That being said, let's see if we can rephrase your desires so members here can assist you better ...

Step 1) You currently have 3 columns of data in your excel sheet
Step 2) You would like to add header titles to each of those 3 columns of data, as well as providing a new, 4th, column header title for the 'Count'
Step 3) You would like to display the data for the 3 column results below that until the value stored in the 3rd column changes
Step 4) You would like to insert a blank row below the 3rd column each time the value stored in the 3rd column changes
Step 5) In addition to this inserted blank row, you would also like to add another header row for the 4 columns below the inserted blank row, similar to Step 2 above
Step 6) You would like to repeat steps 3 - 5 until all Column 3 values have been processed

That sound right? Also, the 4th column you want added ... 'Count' ... are you purposely leaving those cells blank?

Hi Johnny, That is well worded. Thank you for your help.

 

[johnnyL]

Hi Johnny, That is well worded. Thank you for your help.

Just trying to get you the most help. Let us know if I misunderstood your intentions in the post I made,

 

[Rick Rothstein]

Give this macro a try...

Sub SeparateByLocationAddHeaders()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  For R = LastRow To 2 Step -1
    If Cells(R, "C").Value <> Cells(R - 1, 3).Value Then
      Rows(R).Resize(2).Insert
      Cells(R + 1, "A").Resize(, 4) = Array("Product Code", "Description", "LOC", "COUNT")
    End If
  Next
  Rows(1).Insert
  Range("A1:D1") = Array("Product Code", "Description", "LOC", "COUNT")
  Application.ScreenUpdating = True
End Sub

 

[johnnyL]

There you go @natheplas /\ /\ /\

Results: are just as you desired:

Thank You @Rick Rothstein