Normalized Table From Awful Mess 2

[AnotherHiggins]

I am getting an incredibly ugly table from a company for whom we are a vendor. So I don't suppose it would be proper to call up the other company and tell them to fire the person who is sending me the offensive table....

I'm supposed validate that my company really did the work for each of these line items. I'd really like to normalize the data to make my life easier, but I'm having trouble.

I've used Skip's FAQ (faq68-5287) successfully in the past, but there are just too many headers in this monster.

Their layout contains 6 pieces of information for every part ordered. But instead of having a new row for each part ordered, they have a single row per "incident" with room for up to 10 parts (along with all 6 pieces of data for each part). This creates 68 columns instead of 19. They are doing this (I suspect) because they have a subtotal for each incident on that line. Apparently they don't visit Tek-Tips, and therefore haven't been proselytized to about the ease of reporting from properly stored data.

Following are the headers of their table:

Pricing Complete (Not Implemented)
RMA Tracking #
RMA Type
KS&S Incident #
Case Number
Service Method
Account Name
Bill To Account
Actual Ship Date
FedEx Tracking No
RMA Status
Comments to Billing Dept
Item 1 Part Number
Item 1 Description
Item 1 Qty Shipped
Item 1 Qty Returned
Item 1 Unit Price
item 1 Line Total
Item 2 Part Number
Item 2 Description
Item 2 Qty Shipped
Item 2 Qty Returned
Item 2 Unit Price
Item 2 Line Total
Item 3 Part Number
Item 3 Description
Item 3 Qty Shipped
Item 3 Qty Returned
Item 3 Unit Price
Item 3 Line Total
Item 4 Part Number
Item 4 Description
Item 4 Qty Shippped
Item 4 Qty Returned
Item 4 Unit Price
Item 4 Line Total
Item 5 Part Number
Item 5 Description
Item 5 Qty Shipped
Item 5 Qty Returned
Item 5 Unit Price
Item 5 Line Total
Item 6 Part Number
Item 6 Description
Item 6 Qty Shipped
Item 6 Qty Returned
Item 6 Unit Price
Item 6 Line Total
Item 7 Part Number
Item 7 Description
Item 7 Qty Shipped
Item 7 Qty Returned
Item 7 Unit Price
Item 7 Line Total
Item 8 Part Number
Item 8 Description
Item 8 Qty Shipped
Item 8 Qty Returned
Item 8 Unit Price
Item 9 Part Number
Item 8 Line Total
Item 9 Description
Item 9 Qty Shipped
Item 9 Qty Returned
Item 9 Unit Price
Item 9 Line Total
Amount Due
Freight Charge

Id like to get this down to

Pricing Complete (Not Implemented)
RMA Tracking #
RMA Type
KS&S Incident #
Case Number
Service Method
Account Name
Bill To Account
Actual Ship Date
FedEx Tracking No
RMA Status
Comments to Billing Dept
Part Number
Part Description
Part Qty Shipped
Part Qty Returned
Part Unit Price
Amount Due
Freight Charge

Any suggestions other than VBA?


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

Could you use MS Query an perform 8, I think, UNION ALL joins?
[tt]
Select [Pricing Complete (Not Implemented)]
,[RMA Tracking #]
,[RMA Type]
,[KS&S Incident #]
,[Case Number]
,[Service Method]
,[Account Name]
,[Bill To Account]
,[Actual Ship Date]
,[FedEx Tracking No]
,[RMA Status]
,[Comments to Billing Dept]
,[Item 1 Part Number] As 'Part Number'
,[Item 1 Description] As Description
,[Item 1 Qty Shipped] As 'Qty Shipped'
,[Item 1 Qty Returned] As 'Qty returned'
,[Item 1 Unit Price] As 'Unit Price'
,[item 1 Line Total] As 'Line Total
From [Sheet1$]
UNION ALL
Select [Pricing Complete (Not Implemented)]
,[RMA Tracking #]
,[RMA Type]
,[KS&S Incident #]
,[Case Number]
,[Service Method]
,[Account Name]
,[Bill To Account]
,[Actual Ship Date]
,[FedEx Tracking No]
,[RMA Status]
,[Comments to Billing Dept]
,[Item 2 Part Number]
,[Item 2 Description]
,[Item 2 Qty Shipped]
,[Item 2 Qty Returned]
,[Item 2 Unit Price]
,[item 2 Line Total]
From [Sheet1$]
UNION ALL
.....
[/tt]
etc.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

Thanks, Skip. I'll give that a shot and let you know.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I'm finally getting back to this.

Is there limit to the number of UNIONs that can be done in a single query - or maybe the number of characters I can use in a SQL statement in MS Query?

I ask because this is working fine for the first 3 part numbers, but once I add the fourth I get the error

[tab][COLOR=black lightgray]Could not add the table '[RMA_Table]'.[/color]

Here's an example of the SQL for the first two part numbers:

SELECT
[RMA: RMA Tracking #]
, [RMA Type]
, [KS&S Incident #]
, [Case Number]
, [Service Method]
, [Account Name]
, [Bill To Account]
, [Actual Ship Date]
, [FedEx Tracking No]
, [RMA Status]
, [Comments to Billing Dept]
, [Item 1 Part Number] as `Part Number`
, [Item 1 Description] as `Description`
, [Item 1 Qty Shipped] as `Qty Shipped`
, [Item 1 Qty Returned] as `Qty Returned`
, [Item 1 Unit Price] as `Unit Price`
, [item 1 Line Total] as `Line Total`
FROM [RMA_Table]
union all
select
[RMA: RMA Tracking #]
, [RMA Type]
, [KS&S Incident #]
, [Case Number]
, [Service Method]
, [Account Name]
, [Bill To Account]
, [Actual Ship Date]
, [FedEx Tracking No]
, [RMA Status]
, [Comments to Billing Dept]
, [Item 2 Part Number]
, [Item 2 Description]
, [Item 2 Qty Shipped]
, [Item 2 Qty Returned]
, [Item 2 Unit Price]
, [Item 2 Line Total]
FROM [RMA_Table]
union all
....


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Is this a specific Named Range Table and NOT a Sheet Name?

If a Sheet Name then you need the trailing$ as

FROM [RMA_Table$].

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

It is a Named Range of the table.

I'm aiming to automate the whole process since this will be a weekly task, so I'll eventually use a macro to give each newly-received report a standard name in a standard folder (so the same Query can be used each week) and assign a dynamic Named Range.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Would it be better to just use the sheet name?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Another approch would be to use ADODB, query one piece at a time rather than stacking the UNION ALL.

Then use the CurrentRegion property to get the Rows.Count + 1 (next row) and the CopyFormRecordset method to stick the resultset into the stack of accumulated data.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Another thing that you can do, short of the ADODB route, is if you can sucessfully return the data manually, you ought to be able to look at the SQL that was generated as a guide.

There may be a string byte limit, in which case, if you can sucessfully do the above, but not the full monty, you're probably stuck with ADODB. I can post you an example from something I am currently working on, if need be.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

call me at
817....
371....
9285...
if you think I might be able to chat you thru.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[combo]

If you could use ms access, this could be relatively simple:
- link or import table to ms assess,
- create 10 queries to import each item details, remove item number from header, add item number as new column,
- union those queries,
- export to excel.

combo

 

[xlbo]

Any reason you don;t want VBA John - don't think this would be too tricky either using ADO or a set of loops.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

...just on spec - see how this works for ya:

Sub CleanThatCrap()
Dim CleanSht As Worksheet, XtinaSht As Worksheet
Dim i As Long, lRow As Long, NextRow As Long

Const stCol = 1
Const midCol = 12
Const enCol = 65
Const loopGrp = 5

'change this to reference a specific sheet if you want
Set XtinaSht = ActiveSheet

Set CleanSht = Worksheets.Add

'Feel free to use your own method of getting the last row of data
lRow = XtinaSht.Cells(65536, 1).End(xlUp).Row

'Assumes headers on destination sheet
NextRow = 2

With XtinaSht
    'Assumes headers on source sheet
    For i = 2 To lRow
        For x = 1 To 49 Step 6
            If .Cells(i, midCol + (x - 1)).Value <> "" Then
                'part exists, copy over
                .Range(.Cells(i, 1), .Cells(i, midCol - 1)).Copy Destination:=CleanSht.Cells(NextRow, 1)
                .Range(.Cells(i, midCol + (x - 1)), .Cells(i, midCol + (x - 1) + loopGrp)).Copy Destination:=CleanSht.Cells(NextRow, midCol)
                NextRow = NextRow + 1
            End If
        Next x
    Next i
End With
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question