A Challenge for you Excel experts

[mljohn]

I have a large list of parts that I want to change how the data is laid out.

It was laid out by the part number and which machines went to that part number. The machines were all together in one cell.

Now I want to have each machine have it's own row.

I'll need a way to automate the process since I have so many records.

I have a link to my Box.net account that shows an example spreadsheet.

http://www.box.net/shared/cl8l0vi9qh

I hope you are up to the challenge, I sure need the help.

Thanks

Matt

 

[hiker95]

mljohn,

Thanks for the workbook.


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Category	Printer	Compatible with	OemPartNo	MSEPartNo	PremiumPartNo	PageYield	Price	MSRP	StreetPrice
2	TONER	BROTHER	DCP-1200, 1400, Fax 4750, 5750, 8350p, 8750p, HL 1030, 1230, 1240, 1250, 1270n, 1435, 1440, 1470n	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
3	TONER	BROTHER	DCP-7020, IntelliFax-2820, 2910,2920, HL-2040, 2070N, MFC-7220,7225N, 7420,7820N	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
4	TONER	BROTHER	DCP-7030, 7040, 7045N, HL-2140, 2150N, 2170W, MFC-7320, 7340, 7345DN,7345N, 7440N, 7840W	TN360	02-03-3616	02-03-3604	2,600	$29.50	$51.00	$71.99
5
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Category	Printer	Compatible with	OemPartNo	MSEPartNo	PremiumPartNo	PageYield	Price	MSRP	StreetPrice
2	TONER	BROTHER	DCP-1200	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
3	TONER	BROTHER	1400	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
4	TONER	BROTHER	Fax4750	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
5	TONER	BROTHER	5750	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
6	TONER	BROTHER	8350p	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
7	TONER	BROTHER	8750p	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
8	TONER	BROTHER	HL1030	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
9	TONER	BROTHER	1230	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
10	TONER	BROTHER	1240	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
11	TONER	BROTHER	1250	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
12	TONER	BROTHER	1270n	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
13	TONER	BROTHER	1435	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
14	TONER	BROTHER	1440	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
15	TONER	BROTHER	1470n	DR400	58-03-4014	58-03-4010	20,000	$43.00	$175.00	$186.99
16
17
18
19	TONER	BROTHER	DCP-7020	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
20	TONER	BROTHER	IntelliFax-2820	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
21	TONER	BROTHER	2910	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
22	TONER	BROTHER	2920	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
23	TONER	BROTHER	HL-2040	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
24	TONER	BROTHER	2070N	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
25	TONER	BROTHER	MFC-7220	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
26	TONER	BROTHER	7225N	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
27	TONER	BROTHER	7420	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
28	TONER	BROTHER	7820N	DR350	58-03-3514	58-03-3510	12,000	$47.00	$95.00	$119.99
29
30
31
32	TONER	BROTHER	DCP-7030	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
33	TONER	BROTHER	7040	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
34	TONER	BROTHER	7045N	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
35	TONER	BROTHER	HL-2140	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
36	TONER	BROTHER	2150N	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
37	TONER	BROTHER	2170W	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
38	TONER	BROTHER	MFC-7320	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
39	TONER	BROTHER	7340	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
40	TONER	BROTHER	7345DN	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
41	TONER	BROTHER	7345N	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
42	TONER	BROTHER	7440N	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
43	TONER	BROTHER	7840W	TN360	2/3/3616	2/3/3604	2,600	$29.50	$51.00	$71.99
44
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 03/26/2011
' http://www.mrexcel.com/forum/showthread.php?t=538961
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, a As Long, Sp, s As Long, H As String, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
wR.Range("A1:J1").Value = w1.Range("A1:J1").Value
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
NR = 2
For a = 2 To LR Step 1
  H = ""
  H = Replace(w1.Cells(a, 3), " ", "")
  Sp = Split(H, ",")
  s = UBound(Sp) + 1
  wR.Range("A" & NR).Resize(s, 2).Value = w1.Range("A" & a).Resize(, 2).Value
  wR.Range("C" & NR).Resize(s).Value = Application.Transpose(Sp)
  wR.Range("D" & NR).Resize(s, 7).Value = w1.Range("D" & a).Resize(, 7).Value
  NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row + 3
Next a
wR.Range("C2:G" & NR + s).HorizontalAlignment = xlCenter
wR.Range("H2:J" & NR + s).NumberFormat = "[$$-409]#,##0.00_);([$$-409]#,##0.00)"
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[mljohn]

Hiker95 is amazing!!!!

Thank you.

When I saw the results when I ran the macro I felt like Homer Simpson when he thinks of Donuts.

This is one of the most beautiful things I have ever seen.

I never cease to be amazed at the quality of Excel experts I find on this site!!!

Thank you again, Hiker95

Matt Johnson

 

[Norie]

Re: Hiker95 is amazing!!!!

Typed it, seems to work, so may as well post it.

Option Explicit
 
Sub TransposeStuff()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim NoItems As Long
Dim arrVals
 
    Set wsSrc = Worksheets("Sheet1")

    Set wsDst = Worksheets.Add

    Set rngSrc = wsSrc.Range("A2")
    
    Set rngDst = wsDst.Range("A2")
 
    rngSrc.Offset(-1).EntireRow.Copy rngDst.Offset(-1)
 
    While rngSrc.Value <> ""
 
        arrVals = Split(rngSrc.Offset(, 2), ",")
        NoItems = UBound(arrVals) + 1
        rngSrc.Resize(, 2).Copy rngDst.Resize(NoItems, 2)
        
        rngDst.Offset(, 2).Resize(NoItems) = Application.Transpose(arrVals)
        
        rngSrc.Offset(, 3).Resize(, 7).Copy rngDst.Offset(, 3).Resize(NoItems, 7)
        
        Set rngSrc = rngSrc.Offset(1)
        
        Set rngDst = rngDst.Offset(NoItems)
 
    Wend
 
    rngDst.Resize(, 10).EntireColumn.AutoFit
    
    Application.CutCopyMode = False
    
End Sub

 

[mljohn]

Thank you this one also worked. However I liked how Hiker95 put spaces in between the part numbers to keep all the same part numbers together.

 

[hiker95]

Matt Johnson,

Thank you very much.

Glad I could help.

Come back anytime.

 

[mljohn]

Another question:

I noticed that when I ran your macro that some of the machines didn't have their full name. See the 2 examples. The top one they didn't enter the "DCP-" on the 7040 and 7045N. The "HL-" was left off of the 2150N and the 2170W. The bottom group they did enter everything correctly and took no shortcuts.

Is there a way to have the program look for this and fix it. The format seems to be consistent.

Also, is there a way to highlight what I want to apply the macro to?


DCP-7030
7040
7045N
HL-2140
2150N
2170W
MFC-7320
7340
7345DN
7345N
7440N
7840W



DCP-8020
DCP-8025D
HL-1650
HL-1650N
HL-1650NPLUS
HL-1670N
HL-1850
HL-1870n
HL-5040
HL-5050
HL-5050LT
HL-5070N
MFC-8420
MFC-8420D
MFC-8820D
MFC-8820DN

 

[hiker95]

mljohn,

I will need to see your actual raw data.

You can post another workbook on box.net containing the raw data (sensative data changed), and the actual manually formatted output on worksheet Restuls.

 

[mljohn]

Thanks Hiker95. Here is the link

http://www.box.net/shared/7ivbgmvhg1

Matt

 

[hiker95]

mljohn,

Your latest posted workbook on box.net only has one worksheet, but no raw data.


I will need to see your actual raw data.

You can post another workbook on box.net containing the raw data (sensative data changed), and the actual manually formatted output on worksheet Results.