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

 

[Peter_SSs]

Try this in a copy of your workbook. I wasn't sure if you really wanted the first result to stay in column AM and the second one to be in column AP (that is to skip 2 columns). If you do, uncomment the the line near the end of the code.

I have assumed your data is already sorted by columns A and then M per your sample data.

<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>    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> 2 <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, "AN").Resize(, c).Value = _<br>                Cells(r, "AM").Resize(, c).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><SPAN style="color:#007F00">'    Columns("AN:AO").Insert '<- Not sure about this</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[chadski778]

Thanks I think we're nearly there.

I want the first result to stay in column AM and the second to be in column AP and the third AQ etc (skips two columns). At present the second result is landing in AN and the third in AO etc even when i take out the green bit of code at the end?

 

[Peter_SSs]

Thanks I think we're nearly there.

I want the first result to stay in column AM and the second to be in column AP and the third AQ etc (skips two columns). At present the second result is landing in AN and the third in AO etc even when i take out the green bit of code at the end?

I did not suggest you take out the green code, I suggested that you uncomment it. That is, remove the ' from the beginning of the line.

The ' means to treat the line as a comment, so it performs no action as it stood in my code. Removing the line altogether changes nothing.

If you re-insert that line but remove the ' at the start of the line (don't remove the other one towards the end of the line because what follows that really is a comment) then that line should insert 2 new columns, making the second result move to column AP.

 

[chadski778]

The macro seems to be shifting the contents of the columns AN and AO by two columns to the right. I would like AN and AO to be untouched. Is there any way that the duplicates in coulumn AM can be moved to AP, AQ, AR, AS, AT etc without moving anything from columns AN and AO? Other than that it looks like it is working.

Many thanks for your time and effort. It's looking good

 

[Peter_SSs]

Before the code is run, where do you have data in columns AN and AO?
Presumably it is the moving of this data that is the problem with my current code?
Is it in the rows we are dealing with or above/below that data area?

 

[Peter_SSs]

Perhaps my previous questions are irrelevant. Try this version.

<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>    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> 2 <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>            Cells(r - 1, "AP").Value = _<br>                    Cells(r, "AM").Value<br>            c = c + 1<br>            <SPAN style="color:#00007F">If</SPAN> c > 1 <SPAN style="color:#00007F">Then</SPAN><br>                Cells(r - 1, "AQ").Resize(, c - 1).Value = _<br>                    Cells(r, "AP").Resize(, c - 1).Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><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></FONT>

 

[chadski778]

That's brilliant.

Thank you

 

[chadski778]

The last macro worked brilliantly, thanks. Now I need it tweaked a little bit. It's the same conditions as before but now if there are duplicates in A & M but not in AM I need the contents of columns AL, AM, AN and AO to move to AP, AQ, AR and AS keeping the same order. If there is more than 1 duplicate they need to move to AT, AU, AV and AW etc and the duplicates deleted as before. I've tried to tweak it but it's not working.

 

[Peter_SSs]

Any chance of another small set of sample data and expected results to demonstreate this new requirement?

Instead of putting the original data and the results in separate sections of the same sheet (column headings became a bit confusing) just post 2 screen shots (Before and After) with everything in their correct columns/rows. You should still hide the irrelevat columns.