Formatting problem

[chadski778]

In my table I have multiple rows where the value in column A and column M is the same but the values in column AM are different. I would like the different values in AM to be added to the end of the top row and then the duplicate lines deleted. It is easier to understand by looking at the spreadsheet. Can this be done? It is simple reformatting but very labour intensive because there are about 1500 lines in the table.

Excel Workbook
	A	M	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	COLUMN A	COLUMN M	COLUMN AM				COLUMN A	COLUMN M	COLUMN AM	COLUMN AP	COLUMN AQ	COLUMN AR	COLUMN AS	COLUMN AT	COLUMN AU
2	GPC Code	FORM CODE	SP NAME				GPC Code	FORM CODE	SP NAME
3	140001760898	140003430573	ATF 134 FE				140001760898	140003430573	ATF 134 FE	MB ATF 134 FE
4	140001760898	140003430573	MB ATF 134 FE		?		140001786620	140002274173	FormulaShell Automatic Transmission Fluid	Premium Choice Automatic Transmission Fluid	Pzl Automatic Transmission Fluid	QS Automatic Transmission Fluid	Turbo Automatic Transmission Fluid
5	140001786620	140002274173	FormulaShell Automatic Transmission Fluid				140001957448	140002396580	Advance 4T 5W-30 (SF)	Advance 4T AX3 Cold Start 5W-30 (SG/MA)
6	140001786620	140002274173	Premium Choice Automatic Transmission Fluid				140001958754	140002055102	Donax TX (USA/Can/Mex)
7	140001786620	140002274173	Pzl Automatic Transmission Fluid				140001958754	140002252221	ATF 134	MB ATF 134
8	140001786620	140002274173	QS Automatic Transmission Fluid				140001958754	140003430573	ATF 134 FE	MB ATF 134 FE
9	140001786620	140002274173	Turbo Automatic Transmission Fluid
10	140001957448	140002396580	Advance 4T 5W-30 (SF)
11	140001957448	140002396580	Advance 4T AX3 Cold Start 5W-30 (SG/MA)
12	140001958754	140002055102	Donax TX (USA/Can/Mex)
13	140001958754	140002252221	ATF 134
14	140001958754	140002252221	MB ATF 134
15	140001958754	140003430573	ATF 134 FE
16	140001958754	140003430573	MB ATF 134 FE
17
Sheet1

Thanks

Chad

 

[chadski778]

Excel Workbook
	A	M	AL	AM	AN	AO
1
2			BEFORE
3
4	GPC Code	FORM CODE	SP CODE	SP NAME	SP STATUS	SP OWNER
5	GB100604	140003442737	001E2256	Advance 4T AJ 10W-30 for Astra (SL/MA)	Active - Generic	MR A
6	GB100604	140003442737	001D8579	Advance 4T AX5 10W-30 (SL/MA)	Active - Generic	MR B
7	GB100604	140002102369	228C8511	Suzuki ATV Synthetic Blend 5W-30 (SJ, MA)	Active - Customer specific	MR C
8	140001957448	140002396580	001B1714	Advance 4T 5W-30 (SF)	To be deleted	MR D
9	140001957448	140002396580	001E1157	Advance 4T AX3 Cold Start 5W-30 (SG/MA)	Active - Generic	MR E
10	GB100562	140002088852	001C7857	MotoMaster Formula1 10W-40 Motorcycle Oil(SJ/JASO MA)	Active - Customer specific	MR F
11	GB100562	140002088852	001C7855	MotoMaster Formula1 ATV 10W-40(SJ/JASO MA)	Active - Customer specific	MR G
12	GB100562	140002088852	001C7430	Suzuki Motorcycle Engine Oil SAE 10W40 (SJ, MA)	Active - Customer specific	MR H
13
14
C-F-SP (2)

Excel Workbook
	A	M	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1
2			AFTER
3
4	GPC Code	FORM CODE	SP CODE	SP NAME	SP STATUS	SP OWNER	SP CODE 1	SP NAME 1	SP STATUS 1	SP OWNER 1	SP CODE 2	SP NAME 2	SP STATUS 2	SP OWNER 2
5	GB100604	140003442737	001E2256	Advance 4T AJ 10W-30 for Astra (SL/MA)	Active - Generic	MR A	001D8579	Advance 4T AX5 10W-30 (SL/MA)	Active - Generic	MR B
6	GB100604	140002102369	228C8511	Suzuki ATV Synthetic Blend 5W-30 (SJ, MA)	Active - Customer specific	MR C
7	140001957448	140002396580	001B1714	Advance 4T 5W-30 (SF)	To be deleted	MR D	001E1157	Advance 4T AX3 Cold Start 5W-30 (SG/MA)	Active - Generic	MR E
8	GB100562	140002088852	001C7857	MotoMaster Formula1 10W-40 Motorcycle Oil(SJ/JASO MA)	Active - Customer specific	MR F	001C7855	MotoMaster Formula1 ATV 10W-40(SJ/JASO MA)	Active - Generic	MR G	001C7430	Suzuki Motorcycle Engine Oil SAE 10W40 (SJ, MA)	Active - Customer specific	MR H
9
10
11
12
C-F-SP

It doesn't matter about the new column headings I can always put them in later.
Thanks

 

[Peter_SSs]

Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 4 <SPAN style="color:#007F00">'<- Cols AL:AO</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> r = lr <SPAN style="color:#00007F">To</SPAN> 5 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> Cells(r, "A").Value = Cells(r - 1, "A").Value And _<br>                Cells(r, "M").Value = Cells(r - 1, "M").Value <SPAN style="color:#00007F">Then</SPAN><br>            c = c + 1<br>            Cells(r - 1, "AL").End(xlToRight).Offset(, 1).Resize(, c * Cols) _<br>                .Value = Cells(r, "AL").Resize(, c * Cols).Value<br>            Rows(r).Delete<br>        <SPAN style="color:#00007F">Else</SPAN><br>            c = 0<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br></FONT>

 

[chadski778]

The first thing to say is it works! Thak you very much.
The extra problem I have is that the columns of data that are required to be rearranged will not always be in the same columns

In my data:
Col Name Col headings
Col A = GPC Code
Col M = FORM CODE
Col AL = SP CODE
Col AM = SP NAME
Col AN = SP STATUS
Col AO = SP OWNER

Is there a way of carrying out the same exercise using the column headings so that the column names are irrelevant?

 

[Peter_SSs]

The first thing to say is it works! Thak you very much.
The extra problem I have is that the columns of data that are required to be rearranged will not always be in the same columns

In my data:
Col Name Col headings
Col A = GPC Code
Col M = FORM CODE
Col AL = SP CODE
Col AM = SP NAME
Col AN = SP STATUS
Col AO = SP OWNER

Is there a way of carrying out the same exercise using the column headings so that the column names are irrelevant?

Provided the "SP CODE", "SP NAME", "SP STATUS" & "SP OWNER" columns are consecutive columns, then try this modification.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange2()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> cGPC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cForm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cSPC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> HeaderRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 4 <SPAN style="color:#007F00">'<- Change to suit your header row</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> Cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 4      <SPAN style="color:#007F00">'<- No. of columns to move</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Rows(HeaderRow)<br>        cGPC = .Find(What:="GPC Code", LookIn:=xlValues, LookAt:=xlWhole, _<br>            MatchCase:=False, SearchFormat:=False).Column<br>        cForm = .Find(What:="Form Code", LookIn:=xlValues, LookAt:=xlWhole, _<br>            MatchCase:=False, SearchFormat:=False).Column<br>        cSPC = .Find(What:="SP Code", LookIn:=xlValues, LookAt:=xlWhole, _<br>            MatchCase:=False, Search<SPAN style="color:#00007F">For</SPAN>mat:=False).Column<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    lr = Cells(Rows.Count, cGPC).End(xlUp).Row<br>    For r = lr <SPAN style="color:#00007F">To</SPAN> HeaderRow + 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> Cells(r, cGPC).Value = Cells(r - 1, cGPC).Value And _<br>                Cells(r, cForm).Value = Cells(r - 1, cForm).Value <SPAN style="color:#00007F">Then</SPAN><br>            c = c + 1<br>            Cells(r - 1, cSPC).End(xlToRight).Offset(, 1).Resize(, c * Cols) _<br>                .Value = Cells(r, cSPC).Resize(, c * Cols).Value<br>            Rows(r).Delete<br>        <SPAN style="color:#00007F">Else</SPAN><br>            c = 0<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[chadski778]

Thanks for all the help you have given me, I really appreciate it. I can envisage getting similar problems at work with slight variations on the data. I need to build up a library of possible scenarios and solutions. If I have a problem similar to the ones you have helped me with shall I post a reply from this message chain?

Thanks again

Chad

 

[Peter_SSs]

If I have a problem similar to the ones you have helped me with shall I post a reply from this message chain?

It depends on how similar the next problem is.

If it is very similar, you could post in this thread and explain the slight variation.

If it is only slightly similar then I would suggest starting a new thread and perhaps add a link to this thread from your new thread if you think potential helpers would gain understanding about your new problem by being aware of this thread.