Need help with a loop

[EngSantiago]

Hello coders,

I'm working on macro do fix some columns and report but I'm stuck at the following. The below is an example of the report but depending on the report, it may have over 1K rows. The serial numbers on column 4 are associated with the Part number on column 2. That is, the serial numbers with level 2 on column 1 belong to the part number with level 1 above that is above the serial numbers. I would like to copy the data on columns 2 and 3 in the empty cells below for each serial number and later on delete all the rows with level 1. The amount of serial numbers per Part Number varies so I think a loop will do the trick but I just don't know how where to begin as I have never had to create a loop before.

Level	Part Number	QTY	Serial Number
0	555555555-009
1	777777777-001	1 ea
2			04025C103K
2			04025C103L
2			04025C103M
1	666666666-001	3 ea
2			12065C103K
2			12065C103L
2			12065C103M
2			12065C103N

The output should look like this:

Level	Part Number	QTY	Serial Number
0
2	777777777-001	1 ea	04025C103K
2	777777777-001	1 ea	04025C103L
2	777777777-001	1 ea	04025C103M
2	666666666-001	2 ea	12065C103K
2	666666666-001	2 ea	12065C103L
2	666666666-001	2 ea	12065C103M
2	666666666-001	2 ea	12065C103N

Any help is appreciated. I'll be trying a couple of things in the mean time.

Thanks

 

[Dossfm0q]

What about

Book1
	A	B	C	D
1	Level	Part Number	QTY	Serial Number
2	0	555555555-009
3	1	777777777-001	1 ea
4	2			04025C103K
5	2			04025C103L
6	2			04025C103M
7	1	666666666-001	2 ea
8	2			12065C103K
9	2			12065C103L
10	2			12065C103M
11	2			12065C103N
12
13
14	Level	Part Number	QTY	Serial Number
15	2	777777777-001	1 ea	04025C103K
16	2	777777777-001	1 ea	04025C103L
17	2	777777777-001	1 ea	04025C103M
18	2	666666666-001	2 ea	12065C103K
19	2	666666666-001	2 ea	12065C103L
20	2	666666666-001	2 ea	12065C103M
21	2	666666666-001	2 ea	12065C103N
Sheet1

Cell Formulas
Range	Formula
B15:B21	B15	=IF(A15<>"",OFFSET(B2,AGGREGATE(14,6,($B$2:B4<>"")*(ROW($B$2:B4)),1)-ROW(B2),0),"")
C15:C21	C15	=IF(A15<>"",OFFSET(C2,AGGREGATE(14,6,($C$2:C4<>"")*(ROW($C$2:C4)),1)-ROW(C2),0),"")

 

[Peter_SSs]

See if this (no looping) does what you want. Test with a copy of your data.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Rearrange()
  With ActiveSheet.UsedRange
    With .Columns(2).Resize(, 2)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
      .Value = .Value
      .Rows(2).ClearContents
    End With
    .AutoFilter Field:=1, Criteria1:="1"
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

 

[EngSantiago]

See if this (no looping) does what you want. Test with a copy of your data.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Rearrange()
  With ActiveSheet.UsedRange
    With .Columns(2).Resize(, 2)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
      .Value = .Value
      .Rows(2).ClearContents
    End With
    .AutoFilter Field:=1, Criteria1:="1"
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

This step here

      .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"

is filling in the empty cells on the column C (QTY) but not the but not the values on column B (part number).

 

[Peter_SSs]

is filling in the empty cells on the column C (QTY) but not the but not the values on column B (part number).

So column B must not be completely empty. Can you tell us more about what is actually in those column B cells?

 

[EngSantiago]

So column B must not be completely empty. Can you tell us more about what is actually in those column B cells?

That's right. They have some leading spaces.

 

[Peter_SSs]

They have some leading spaces.

So there could be more than one space character in any of those 'empty' cells.
Also, the term 'leading spaces' normally implies that something else follows those spaces. Is that so for your 'empty' cells?

How do the space characters get there in the first place? Can they be eliminated from the initial report?

 

[EngSantiago]

So there could be more than one space character in any of those 'empty' cells.
Also, the term 'leading spaces' normally implies that something else follows those spaces. Is that so for your 'empty' cells?

How do the space characters get there in the first place? Can they be eliminated from the initial report?

I was able to make get clean data and the code is working fine. Now, there is a change in gears. I'm not going to use the filter and delete functions from your code because I don't want to delete the higher levels. Instead, I inserted a new row on column A named "Next Higher Assembly". In this new column A, I want to call out the higher assembly above each line item. i.e. based on the below table cell A3 and A6 (for 777777777-001 and 777777777-002) should call out 555555555-009, cells A4 and A5 (for 666666666-001 and 666666666-002) should call out 777777777-001, A8 (for 444444444-002) should call out 444444444-001, and so on.

Next Higher Assembly	Level	Part Number	QTY	Serial Number
	0	555555555-009	1
	1	777777777-001	1
	2	666666666-001	1	666666666-001
	2	666666666-002	1	666666666-002
	1	777777777-002	4
	2	444444444-001	1
	3	444444444-002	1	444444444-002
	2	777777777-003	2
	3	888888888-001	2	888888888-001

Thanks for your help! Much appreciated.

 

[Peter_SSs]

See if this is it.

Sub NextHigher()
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .Formula = "=XLOOKUP(B2-1,B$1:B1,C$1:C1,"""",0,-1)"
    .Value = .Value
  End With
End Sub

My results:

EngSantiago.xlsm
	A	B	C	D	E
1	Next Higher Assembly	Level	Part Number	QTY	Serial Number
2		0	555555555-009	1
3	555555555-009	1	777777777-001	1
4	777777777-001	2	666666666-001	1	666666666-001
5	777777777-001	2	666666666-002	1	666666666-002
6	555555555-009	1	777777777-002	4
7	777777777-002	2	444444444-001	1
8	444444444-001	3	444444444-002	1	444444444-002
9	777777777-002	2	777777777-003	2
10	777777777-003	3	888888888-001	2	888888888-001
Sheet1