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

 

[mljohn]

Sorry; here is the new link

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

 

[hiker95]

mljohn,


Sample new raw data on 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-7030, 7040, 7045N,HL-2140, 2150N, 2170W, MFC-7320, 7340, 7345DN, 7345N, 7440N, 7840W	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
3	TONER	BROTHER	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-8420 D, MFC-8820D, MFC-8820DN	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
4
Sheet1

After the same macro on 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-7030	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
3	TONER	BROTHER	7040	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
4	TONER	BROTHER	7045N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
5	TONER	BROTHER	HL-2140	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
6	TONER	BROTHER	2150N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
7	TONER	BROTHER	2170W	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
8	TONER	BROTHER	MFC-7320	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
9	TONER	BROTHER	7340	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
10	TONER	BROTHER	7345DN	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
11	TONER	BROTHER	7345N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
12	TONER	BROTHER	7440N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
13	TONER	BROTHER	7840W	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
14
15
16
17	TONER	BROTHER	DCP-8020	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
18	TONER	BROTHER	DCP-8025D	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
19	TONER	BROTHER	HL-1650	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
20	TONER	BROTHER	HL-1650N	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
21	TONER	BROTHER	HL-1650NPLUS	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
22	TONER	BROTHER	HL-1670N	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
23	TONER	BROTHER	HL-1850	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
24	TONER	BROTHER	HL-1870n	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
25	TONER	BROTHER	HL-5040	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
26	TONER	BROTHER	HL-5050	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
27	TONER	BROTHER	HL-5050LT	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
28	TONER	BROTHER	HL-5070N	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
29	TONER	BROTHER	MFC-8420	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
30	TONER	BROTHER	MFC-8420D	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
31	TONER	BROTHER	MFC-8820D	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
32	TONER	BROTHER	MFC-8820DN	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
33
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).

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.

 

[hiker95]

mljohn,

I noticed that when I ran your macro that some of the machines didn't have their full name. Is there a way to have the program look for this and fix it.

Yes, I would need a complete list of all the possable items in Compatible with.

 

[mljohn]

Here is a link to my data

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


Thanks

 

[hiker95]

mljohn,

I ran the original macro on your latest workbook. It works correctly.

 

[mljohn]

I'm sorry, I'm not saying that your macro caused the problem. I'm saying that until I ran your macro I didn't notice that my vendor did not enter the data correctly and I wanted to know if you could help me add the parts of the machine names that they didn't add.

 

[hiker95]

mljohn,

I noticed that when I ran your macro that some of the machines didn't have their full name. Is there a way to have the program look for this and fix it.

I wanted to know if you could help me add the parts of the machine names that they didn't add.

Yes, I would need a complete list of all the possible complete items for column Compatible with.

 

[mljohn]

I'm not sure what you are asking for. I gave you a link to my complete spreadsheet.

Matt

 

[hiker95]

mljohn,

I need the complete list, like this, for all parts/part numbers:
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

 

[mljohn]

I wish I could but that is what I need.

The data entry people took short cuts when entering the data.

There is a pattern. For example: Row #271

HL-5130
5140
5150D These are all the HL- machines
5150DLT They need the "HL-" added to them
5170DN
5170DNLT

DCP-8040 These are all the DCP- machines
8045D They need the "DCP-" added to them

MFC-8220 These are all the MFC- machines
8440 They need the "MFC-" added to them
8640D
8840D
8840DN

If you go thru the RESULTS worksheet you will see the pattern.

Is there a way to have Excel automatically add the missing data?
Also since this may make changes that were not needed, is it possible to mark the ones that were changed? Such as an extra column with an "X" or color the cell that was changed.

Here is the link: http://www.box.net/shared/n97y7f0z6l

thanks

Matt