How can I auto fill a last value?

[daddybirdian]

Here's my dilemna.

I've got 4000 lines of data, and it's broken down into 1000 or so groups (numbers of rows per group varies), and each group is separated by a blank row.

For instance:

Group A
Part Product Description Qty
ABC123 blah blah blah 2
ABC124 blah blah blah 1
ABC125 blah blah blah 2

Group A
Part Product Description Qty
ABC123 blah blah blah 2
ABC124 blah blah blah 1
ABC126 blah blah blah 2
ABC127 blah blah blah 2
ABC128 blah blah blah 2
ABC129 blah blah blah 2

I want to replace each row's part number with the group number found in the closest row above.

Does that make sense?

 

[facethegod]

I'm not sure I understand:

Care to post an example of your desired results..?



HTH

 

[daddybirdian]

Face,

I'm doing some data migration for an inventory project, and my customer's inventory export from their old system put the data into a format that I need to massage before I can bring it into the new system.

I'm dealing with parts that have Bills of Materials, so each master part has a list of parts under it that get consumed when the master part is made.

Ideally, my part looks like this :

Row 1:Part number: BB2001
Row 2:Part Description: Bike
Row 3:Bill of Materials:
Row 4:Part Description Qty
Rpw 5:B201 Chain 1
Row 6:C250 Ties 2
Row 7:AB123 Seat 1
Row 8:SP123 Spokes 12

Keep in mind that I have 4000+ lines of data, and each master part may have a different number of lines (parts on the Bill of Materials).

The inventory program that I am importing into requires that each Bill of Materials part row has the master part number in the first column. So, my data needs to look like this:

Part number: BB2001
Part Description: Bike
Bill of Materials:
Row 1:Master Part Part Description Qty
Row 2: BB2001 B201 Chain 1
Row 3: BB2001 C250 Ties 2
Row 4: BB2001 AB123 Seat 1
Row 5: BB2001 SP123 Spokes 12

I can certainly insert a new column (column A) for the Master part number listing, but how do I get the Master Part number of BB2001 in there? Each Master part has a different number of rows (Bill of Material parts), so I can't just copy a formula that would look at a certain row in the list for the master part number.

If all of my parts had the same amount of Bill of Materials rows, this would be so much easier. This is an example of the first part in the list.

Does that make any more sense?

 

[facethegod]

In not know what your data set looks like.... try this macro:

Option Explicit
Sub try()
On Error Resume Next
Dim ar(), ar1(), i, j, Lrow As Variant
Dim c, rng As Range
Set c = Range("A:A").SpecialCells(xlCellTypeBlanks)
For Each rng In c
i = i + 1
ReDim Preserve ar(1 To i)
ar(i) = rng.Offset(1, 0).CurrentRegion.Address
ReDim Preserve ar1(1 To i)
ar1(i) = Range(ar(i)).Cells(1, 1)
Next
Columns(1).Insert
For i = LBound(ar) To UBound(ar)
Lrow = Range(ar(i)).Offset(0, 1).End(xlDown).Row - Range(ar(i)).Row + 1
With Range(ar(i))
.Resize(Lrow, 1).Value = ar1(i)
End With
Next
End Sub