Excel - How to export with repeating values

[pheffley]

I have several workbooks that contain several worksheets. They were created sequentially by workbook/worksheet with headings only in the first workbook. The data is grouped by an Operation Number "OP NBR". I need to import this data into an Access database and have the OP NBR have it's own column that repeats for each record. I also need the Part#, Code1, and Code2 columns to repeat for each record. This is an extremely huge data set and to recreate it would almost be impossible. I would prefer to do this in Excel, bu if can be done easier in Access, that is fine too (The data below is example data only).

Part#	Job#	        Code1	Code2   Code3
Op Nbr:	10			
12345B	123467892345	12345R	30	123467892345
	      123467892345			        123467892345
	      123467892345			        123467892345
	      123467892345			        123467892345
	      123467892345			        123467892345 
Op Nbr:	11			
12345B	023467892345	12345R	30	123467892345
	      023467892345			        123467892345
	      023467892345			        123467892345
	      023467892345			        123467892345
	      023467892345			        123467892345

Thanks for any help,

Paul

 

[SkipVought]

Paul,

I'd do it with code. But it can be done with spreadsheet functions.

You need 2 new columns...
[tt]
PartNbr =IF(Part="Op Nbr:","",IF(ISBLANK(Part),F1,Part))
OpNbr =IF(Part="Op Nbr:",Job,G1)
[/tt]
where I have Names the columns using Insert/Name/Create - create name in top row.



Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[pheffley]

I'm pretty green in Excel programming. How exactly would I accomplish the above. Also that takes care of splitting the Part Nbr and Op Nbr, but how do I get the values to repeat?

Thanks so much,

Paul

 

[SkipVought]

They DO repeat!

If your example starts in A1 then in CELL
[tt]
F1: PartNbr
G1: OpNbr
F2: =IF(Part="Op Nbr:","",IF(ISBLANK(Part),F1,Part))
G2: =IF(Part="Op Nbr:",Job,G1)
[/tt]
then copy f2:g2 down thru all rows of data



Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[pheffley]

Okay, cool. Do I have to do this manually for each sheet in each workbook, or is there an easier way? The reason I ask is because I'm dealing with over a Gig of data broken out in 27 excel workbooks each having 3 to 60 sheets. ;( That's a lot of copying/pasting!

Also, Code1 and Code2 need to repeat as well. Also, they could remain the same for each OP NBR or change multiple time for each OP NBR. So, you'll see Code1 and Code2 values populated in one record and then several blanks (maybe 50 rows) then see a different Code2 value (Code1 is the same at this point).

 

[SkipVought]

I hate to write code for situation that might have variations that i don't knwo about. But here goes

Sub Proppogate()
   For Each wb In Workbooks
      For Each ws In wb.Worksheets
         With ws.UsedRange
            r1 = 1
            If .Cells(1, 1).Value = "Part#" Then
               ws.Cells(r1, 6).Value = "PartNbr"
               ws.Cells(r1, 7).Value = "OpNbr"
               ws.Cells(r1, 8).Value = "Code_1"
               ws.Cells(r1, 9).Value = "Code_2"
               r1 = 2
            End If
            For r = r1 To .Rows.Count
               If ws.Cells(r, 1) = "Op Nbr:" Then
                  OpNbr = ws.Cells(r, 2).Value
               Else
                  If ws.Cells(r, 1).Value <> "" Then PartNbr = ws.Cells(r, 1).Value
                  If ws.Cells(r, 3).Value <> "" Then Code1 = ws.Cells(r, 3).Value
                  If ws.Cells(r, 4).Value <> "" Then Code2 = ws.Cells(r, 4).Value
               End If
               ws.Cells(r, 6).Value = PartNbr
               ws.Cells(r, 7).Value = OpNbr
               ws.Cells(r, 8).Value = Code1
               ws.Cells(r, 9).Value = Code2
            Next
         End With
         ws.[A1].CurrentRegion.Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
             xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
             DataOption1:=xlSortNormal
      Next
   Next
End Sub

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[SkipVought]

I missed a reference in the sort...

Sub Proppogate()
   For Each wb In Workbooks
      For Each ws In wb.Worksheets
         With ws.UsedRange
            r1 = 1
            If .Cells(1, 1).Value = "Part#" Then
               ws.Cells(r1, 6).Value = "PartNbr"
               ws.Cells(r1, 7).Value = "OpNbr"
               ws.Cells(r1, 8).Value = "Code_1"
               ws.Cells(r1, 9).Value = "Code_2"
               r1 = 2
            End If
            For r = r1 To .Rows.Count
               If ws.Cells(r, 1) = "Op Nbr:" Then
                  OpNbr = ws.Cells(r, 2).Value
               Else
                  If ws.Cells(r, 1).Value <> "" Then PartNbr = ws.Cells(r, 1).Value
                  If ws.Cells(r, 3).Value <> "" Then Code1 = ws.Cells(r, 3).Value
                  If ws.Cells(r, 4).Value <> "" Then Code2 = ws.Cells(r, 4).Value
               End If
               ws.Cells(r, 6).Value = PartNbr
               ws.Cells(r, 7).Value = OpNbr
               ws.Cells(r, 8).Value = Code1
               ws.Cells(r, 9).Value = Code2
            Next
         End With
         ws.[A1].CurrentRegion.Sort Key1:=ws.Range("E2"), Order1:=xlDescending, Header:= _
             xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
             DataOption1:=xlSortNormal
      Next
   Next
End Sub

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[pheffley]

Like I said, I'm pretty green in Excel programming. Where would I input and run code like this?

Thanks,

Paul

 

[SkipVought]

Put this code in a MODULE in the VB Editor (alt+F11)

Then run from Tools/Macro/Macros - Run Propogate.

If you have all your workbooks open, it will cycle thru all the open workbooks.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]