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,


Per your latest workbook, and instructions (I am displaying a worksheet Compatible with for discussion):

Excel Workbook
	C	K	N
271	HL-5130		There is a pattern. For example: Row #271
272	5140		HL-5130
273	5150D		5150D These are all the HL- machines
274	5150DLT		5150DLT They need the "HL-" added to them
275	5170DN		5170DN
276	5170DNLT		5170DNLT
277	DCP-8040		DCP-8040 These are all the DCP- machines
278	8045D		8045D They need the "DCP-" added to them
279	MFC-8220		MFC-8220 These are all the MFC- machines
280	8440		8440 They need the "MFC-" added to them
281	8640D		8640D
282	8840D		8840D
283	8840DN		8840DN
Compatible with

We can do the above for 12 items per your instructions.



But. The following screenshot, column L, contains a list of the unique items from your lastest column C which contains 3,691 rows of data. There are 1,003 unique items. Column P shows the unique list sorted ascending.


What are we going to do with the the rest?

Excel Workbook
	L	O	P
1	Compatible with UNIQUE contains 1003 unique items		Compatible with UNIQUE SORTED
2	LaserWriter12/640+		5
3	12/640PS		32
4	LaserWriter16/600		40
5	16/600PS		80
6	LaserWriterPro600		104
7	630(EX)		120
8	LaserWriter350(BX)		150
9	DCP-1200		160
10	1400		170
11	Fax-4750		250
12	5750		300
13	8350p		310
14	8750p		320
15	HL-1030		322
16	1230		325
17	1240		330
18	1250		340
19	1270n		400
20	1435		401
21	1440		420
22	1470n		425
Compatible with

 

[mljohn]

Arg!

I don't know what you mean.

I am soooo frustrated with my communication skills.

Example: from row #271

This is what they typed in. The data entry person was lazy. She typed in "HL-5130" and instead of typing "HL-5140" she just typed "5140", etc until she hit the "DCP" machines where she only typed the "DCP-" on the first machine.

If you will look on the "Results" worksheet in the "Compatible with" column you will see the pattern. Sometimes they type in the full machine model and sometimes they take the shortcut and don't fill in the first letters after the first machine.

HL-5130
5140
5150D
5150DLT
5170DN
5170DNLT
DCP-8040
8045D
MFC-8220
8440
8640D
8840D
8840DN

This is what I want.

HL-5130
HL-5140
HL-5150D
HL-5150DLT
HL-5170DN
HL-5170DNLT
DCP-8040
DCP-8045D
MFC-8220
MFC-8440
MFC-8640D
MFC-8840D
MFC-8840DN

This example is just for the Brothers. If you look down the "Compatible with" column you will see the pattern where they don't type in the full model number on every machine.

I don't want to manually fill in all the missing data.

Can we write a visual basic code that finds the models that weren't filled out and fix them?

 

[Norie]

Wouldn't that code need to take what's been entered and match it against all possible variations that might match a correct name?

What if there are conflicts? eg 2 models of printers, each from different manufacturers, both have '5140' in them?

 

[hiker95]

Norie,

Thank you very much.


mljohn,

Can we write a visual basic code that finds the models that weren't filled out and fix them?

Yes, but:

The only way we can fill in all the missing printer model information (and be accurate), we will need a complete list of all the printer models to compare each item in worksheet Results, column C.

We need something like this, for all the printer models:

HL-5130
HL-5140
HL-5150D
HL-5150DLT
HL-5170DN
HL-5170DNLT
DCP-8040
DCP-8045D
MFC-8220
MFC-8440
MFC-8640D
MFC-8840D
MFC-8840DN

 

[mljohn]

OK, I quit. I can't explain this properly.

I need the very list you suggest I provide. That is the whole purpose for this. I can't give it to you if I haven't created it yet.

Can't the code be done one line at a time?

If the cell contains a number with no alpha characters before any numbers and the cell above is not a blank cell and contains alpha characters before numbers then add the characters preceding the "-" character including the "-" character. Such as "HL-".

If not then leave it the way it is.

 

[hiker95]

mljohn,

If the cell contains a number with no alpha characters before any numbers and the cell above is not a blank cell and contains alpha characters before numbers then add the characters preceding the "-" character including the "-" character. Such as "HL-".

If not then leave it the way it is.

Now I understand.



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-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, 8025D, HL-1650, 1650N, 1650NPLUS, 1670N, 1850, 1870n, 5040, 5050, 5050LT, 5070N, MFC-8420, 8420 D, 8820D, 8820DN	DR500	58-03-5014	58-03-5010	20,000	$62.00	$109.00	$198.29
4
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-7030	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
3	TONER	BROTHER	DCP-7040	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
4	TONER	BROTHER	DCP-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	HL-2150N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
7	TONER	BROTHER	HL-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	MFC-7340	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
10	TONER	BROTHER	MFC-7345DN	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
11	TONER	BROTHER	MFC-7345N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
12	TONER	BROTHER	MFC-7440N	DR360	58-03-3614	58-03-3610	12,000	$46.00	$75.00	$99.99
13	TONER	BROTHER	MFC-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 ReorgDataV2()
' hiker95, 03/28/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)"
LR = wR.Cells(Rows.Count, 3).End(xlUp).Row
H = ""
For a = 2 To LR Step 1
  If Cells(a, 3) = "" Then
    H = ""
  ElseIf InStr(Cells(a, 3), "-") > 0 Then
    Sp = Split(Cells(a, 3), "-")
    H = Sp(0) & "-"
  Else
    Cells(a, 3) = H & Cells(a, 3)
  End If
Next a
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.

 

[mljohn]

wonderful! Yes this is better.

I noticed that it took all the spaces out between the machine and model number. Example: Imageclass MF5530 = ImageclassMF5530

Can the spaces not be removed?

Also, I noticed that some of the Canons it did not work on.
I highlighted those in yellow.

Also, it did not work on many others. I guess that might be because those did not have a "-". I highlighted those in orange. I also created a "Sheet 2" with a list of machine names that were not effected.

Can those also be fixed?

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

Thanks

 

[hiker95]

mljohn,


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 ReorgDataV3()
' hiker95, 03/30/2011
' http://www.mrexcel.com/forum/showthread.php?t=538961
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, a As Long, aa 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 = 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
  For aa = LBound(Sp) To UBound(Sp)
    Sp(aa) = Trim(Sp(aa))
  Next aa
  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)"
LR = wR.Cells(Rows.Count, 3).End(xlUp).Row
H = ""
For a = 2 To LR Step 1
  If Cells(a, 3) = "" Then
    H = ""
  ElseIf InStr(Cells(a, 3), "-") > 0 Then
    Sp = Split(Cells(a, 3), "-")
    H = Sp(0) & "-"
  Else
    Cells(a, 3) = H & Cells(a, 3)
  End If
Next a
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV3 macro.



If the above macro is not acceptable, then click on the Post Reply button, and just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.

 

[mljohn]

Thank you for fixing the spaces.

I was worried that I might wear you out. Sorry about that.

Hiker95, you have amazed me with you skill. Sorry about the communication problems.

Thank you for your help. You have gotten me a long ways toward fixing this night mare of price lists that I am buried in.

 

[mljohn]

mljohn,

how do I make this code capture more columns beyond column "J"?

Thanks

Matt