Auto Filling Cells

[ptrifile]

I think this is going to be hard to explain but here goes. I have a spreadsheet that is an export from another program and its very ugly. There is over 17000 rows so doing this manually would be extremely time consuming.

I have a rows for each time a sku is sold that hold various data. After all transactions are shown a blank row shows and then the SKU # is shown. What i would like to do is, either at the begining column (A) or wherever actually is take the SKU number and have it repeated for each line or row entry. Not sure if that makes sense or not but I will try and show below:

Current Export:

SoldWhere Amount Qty etc etc etc
-----blank row---
SKU#102
-----blank row---
SoldWhere Amount Qty etc etc etc
SoldWhere Amount Qty etc etc etc
SoldWhere Amount Qty etc etc etc
SoldWhere Amount Qty etc etc etc
-----blank row----
SKU#100
-----blank row---
-----blank row---
SoldWhere Amount Qty etc etc etc
SoldWhere Amount Qty etc etc etc
-----Blank Row------
SKU#111

I would like it to look like the following:

SKUU#102 SoldWhere Amount Qty etc etc etc

SKU#100 SoldWhere Amount Qty etc etc etc
SKU#100 SoldWhere Amount Qty etc etc etc
SKU#100 SoldWhere Amount Qty etc etc etc
SKU#100 SoldWhere Amount Qty etc etc etc


SKU#111 SoldWhere Amount Qty etc etc etc
SKU#111 SoldWhere Amount Qty etc etc etc


Is there any VBA or some other possible way to get the desired results in Excel without manually copying the SKU numbers down column A?

Thanks for any help or suggestions!

Paul

 

[SkipVought]

Hi,

It may not be possible for you, but in my experience I almost always could find the source data from which the report was generated, and I would access the source data DIRECTLY from Excel via a query.

As anothe option, you might consider contacting the person producing the report, who quite often can spin off a Data file as a .csv.

But if those options are not at all available to you, post your question in forum707 for help with a VBA solution.

 

[ptrifile]

I was afraid of that. Thanks Skip. I am having a hard time trying to get my IT folks to help me out here. Thanks again.

 

[randy700]

How about:

Assuming your current spreadsheet uses columns A thru F and 17000 rows.
1. Add column G and enter numbers 1 thru 17000.
2. Sort on column A to bring your blank rows to the top.
3. Delete the blank rows.
4. Sort on column G in descending order.
5. The top row should be a SKU number.
6. In cell H1, enter =A1
7. In cell H2, enter =IF(LEFT(A2,3)="SKU",A2,C1)
8. Copy the formula down to the last row.
9. Sort on column A, find your SKU lines, and delete them.
10. Sort on column G in ascending order.


Randy

 

[zelgar]

Try this macro

Sub sku_adj()

Dim max As Integer, irow As Integer, sku As String

max = 1700      ' Maxumim number of data
irow = 2        ' Starting Row of Data
sku = ""

'   Insert Blank Column A (Shifts everything to the Right)
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'
For x = irow To max
'  Next check to see if the row contains SKU or data, if SKU change sku to it, else populate col A with SKU number
    If Left(Cells(x, 2), 3) = "SKU" Then sku = Cells(x, 2)
    Cells(x, 1) = sku
Next x

'  Delete rows that are blank or only contain SKU#
irow = 2
Do Until IsEmpty(Cells(irow, 1))
    If Cells(irow, 2) = "" Or Left(Cells(irow, 2), 3) = "SKU" Then Rows(irow).Delete Else irow = irow + 1
Loop

End Sub

 

[ptrifile]

Thanks everyone.

Zelgar, i ran your macro and I seem to only get a new column in A, with no SKU # added in that column. I have attached a screen shot of my spreadsheet to see if maybe that would help. Thank you for your time and any suggestions you may have.

Thanks again!

 

[SkipVought]

Well your original post and your example are significantly different regarding the SKU data, so it's no wonder that you get no SKU value in column A!

 

[SkipVought]

You Here's another thing that I can observe from your example. You or someone before you, has opened the export file using Excel. That's a big problem! It has messed up your data by parsing data incorrectly into cells.

Hopefully you can access the export file. If so, there's hope.

Open a new workbook.

Use the Data > External Files > Text files to IMPORT your data into ONE COLUMN.

Then you'll have to write VBA routine to restructure and parse your data based on a set of rules that will identify each record type a process eac type accordingly to get the record types you want into the proper columns (on another sheet) and ignore the remainder.

Not here, but in Forum707.