Copying inconsistent header fields to matching rows

**edkirk** · 01-01-2013, 04:20 PM

I have a large Excel spreadsheet which is an export of a report from an Accounting software with over 10,000 customer records. I need to get this spreadsheet converted so every record has it's matching customer ID in each customer record/row. Unfortunately this report is formatted where it list the customer ID as a header and above an inconsistent number of records. Sometimes there is 1 record, other times there could be 30. To give you an example of the format, the first customer shows up in row 4 and in cell A4 it list the customer title "Customer:" and then list the Customer ID in cell D4 "123456". Then the first customer record is always listed 3 rows down where it always has the title "Record:" in this case the title in cell A7 and the first data showing in D7 with several more columns of data in that row 7 where I would like to add the Customer ID from cell D4 in each row that has the title "Record:" which for 1st customer only has 2 records, so rows 7 and 8. The next customer starts again in row 9 and has only one record in row 12. So the 3rd customer then shows in row 13 and it happens to have 17. So the next customer starts again in row 31, and first record in row 34. The pattern repeats. I'm at a lost here on what I could do outside of alot of manual work. Any ideas?

**FDibbins** · 01-01-2013, 04:56 PM

Hi ed and welcome to the forum.

Thats quite a mouthful there

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**edkirk** · 01-01-2013, 05:42 PM

I've attached an example file with only 3 customers. My expected outcome is at the bottom below my comments in yellow. Let me know if there are any questions on this. You will see I added another level of complexity than what is described below with a "Location" row that needs to have the Customer ID number tied to it as well.

Originally Posted by edkirk

I have a large Excel spreadsheet which is an export of a report from an Accounting software with over 10,000 customer records. I need to get this spreadsheet converted so every record has it's matching customer ID in each customer record/row. Unfortunately this report is formatted where it list the customer ID as a header and above an inconsistent number of records. Sometimes there is 1 record, other times there could be 30. To give you an example of the format, the first customer shows up in row 4 and in cell A4 it list the customer title "Customer:" and then list the Customer ID in cell D4 "123456". Then the first customer record is always listed 3 rows down where it always has the title "Record:" in this case the title in cell A7 and the first data showing in D7 with several more columns of data in that row 7 where I would like to add the Customer ID from cell D4 in each row that has the title "Record:" which for 1st customer only has 2 records, so rows 7 and 8. The next customer starts again in row 9 and has only one record in row 12. So the 3rd customer then shows in row 13 and it happens to have 17. So the next customer starts again in row 31, and first record in row 34. The pattern repeats. I'm at a lost here on what I could do outside of alot of manual work. Any ideas?

**FDibbins** · 01-01-2013, 05:49 PM

wow that is quite a mess

how are you importing/downloading the data in teh 1st place?

If that is the only way you cat get the data, you will need some VBA code (which i suck at), to untagle the info.

also, you dont need to use "reply with quote", just scroll down until you see the "quick reply" box

**edkirk** · 01-01-2013, 06:00 PM

It's the only report and thus format we can get out the software it comes from. Yes, it is a mess. I have done some Python before in Excel so maybe there is something I can do with it. But still don't know how I'm going to deal with the indeterminate number of records for each customer.

**FDibbins** · 01-01-2013, 06:22 PM

I'll see if some of the VBA experts can offer anything?

**Pete_UK** · 01-01-2013, 08:20 PM

Here's a manual/formula based method:

Insert 3 new columns, A, B, C
Put formulae in A2, B2, C2:
A2: =IF(C2="","Delete","")
B2: =IF(D1="Customer:",G1,B1)
C2: =IF(D2="Location:",D2,IF(E2="Record:",E2,""))
Copy formulae down to bottom of data
Fix values of formulae in A, B, C
Apply autofilter to column A, select "Delete"
Highlight visible rows - click <Delete>
Select All in filter Drop-down
Delete columns D:E
Delete column A
Save As... to give file a different name

This should be easy to record as a macro and then you can re-run it as often as you need.

Hope this helps.

Pete

**edkirk** · 01-01-2013, 09:57 PM

Thanks Pete, that totally worked great. I have one issue still that I'm thinking I'm just going to ignore. Some of the names of the Records are too long and thus get put in the next row. In this current format, these will get deleted out but they should be added on to the name of cell above it. So in the example file I sent, the work "CORNERS" in row 31 should be added on the cell above in row 30 after the "LOCATORS5,6&". I'm guessing there really isn't a way to deal with this. Let me know if you have any ideas. Thanks again for the tip on the other.

**Pete_UK** · 01-01-2013, 10:19 PM

Yes, it also happens on row 7. You can amend the formula in C2 to this:

=IF(D2="Location:",D2,IF(E2="Record:",E2,IF(AND(E1="Record:",E2=""),"Check","")))

Then you will have the word "Check" if that occurs, and this row would not be marked for deletion. You could then scan for that word and manually adjust the cells in column G on the rows above where it occurs (could be tedious), or you could introduce this formula in F2:

=IF(C2="","",IF(C3="Check",G2&" "&G3,IF(C2="Check","",G2)))

which will add the offending word(s) back in, although you might also like to amend the formula in A2 to this:

=IF(OR(C2="",C2="Check"),"Delete","")

so that the "Check" rows are marked for deletion. You will need to fix the values in column F at the same time as A:C, and then afterwards you can copy/paste Special values from F to G to retain the formats, and then delete the contents of column F.

Hope this helps.

Pete

**edkirk** · 01-01-2013, 11:57 PM

Thanks again Pete, those additons worked great.

**mike7952** · 01-01-2013, 07:13 PM

Give this a try

Option Explicit
Sub abc()
 Const shData As String = "sheet1"
 Const LastColumn As String = "T"
 
 Dim a, b, i As Long, ii As Long, iii As Long, n As Long
 Dim sCustID As String
 
 With Worksheets(shData)
    a = .Range("a1", .Cells(.UsedRange.Rows.Count, LastColumn))
 End With
 
 ReDim b(1 To UBound(a), 1 To UBound(a, 2))
 
 For i = 1 To UBound(a)
    If Trim$(a(i, 1)) = "Customer:" Then
        sCustID = a(i, 4)
        n = n + 1
        b(n, 1) = sCustID
        b(n, 2) = Trim$(a(i + 3, 1))
        b(n, 4) = Trim$(a(i + 3, 4))
        For iii = 5 To UBound(a, 2)
            b(n, iii) = Trim$(a(i + 3, iii))
        Next
    End If
    If Trim$(a(i, 1)) = "Location:" Then
        For ii = i + 1 To UBound(a)
            If Trim$(a(ii, 2)) = vbNullString Then Exit For
            n = n + 1
            b(n, 1) = sCustID
            b(n, 2) = Trim$(a(ii, 2))
            b(n, 4) = Trim$(a(ii, 4))
            For iii = 5 To UBound(a, 2)
                b(n, iii) = Trim$(a(ii, iii))
            Next
        Next
    End If
 Next
 With Worksheets(shData)
    a = .Range("a2", .Cells(3, LastColumn))
 End With
 Worksheets.Add After:=Sheets(shData)
 With Range("a1")
    .Resize(UBound(a), UBound(a, 2)) = a
    .Offset(3).Resize(n, UBound(b, 2)) = b
 End With
End Sub

**JBeaucaire** · 01-02-2013, 05:43 AM

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

Copying inconsistent header fields to matching rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Re: Copying inconsistent header fields to matching rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions