Excel Transform data for database import 1

[blackduck]

Sorry I'm unable to explain, so therefore it is hard to search. Here is an example of what I have and what I want it to look like. This will be over hundreds of records.

What the data is:
Code Bore (mm) Bush (mm) Diameter (mm) Diameter (in) Kerf (mm) Rake RPM Teeth Tooth Grind
TP2102540 25 16 210 8-1/4 1.8 15 7000 40 ATB 15
TP2163060 30 16 216 8-1/2 2.0 5 7000 60 ATB 15

How I need it to look like:
TP2102540 Bore (mm) 25
TP2102540 Bush (mm) 16
TP2102540 Diameter (mm) 210
TP2102540 Diameter (in) 8-1/4
TP2102540 Kerf (mm) 1.8
TP2102540 Rake 15
TP2102540 RPM 7000
TP2102540 Teeth 40
TP2102540 Tooth Grind ATB 15
TP2163060 Bore (mm) 30
TP2163060 Bush (mm) 16
TP2163060 Diameter (mm) 216
TP2163060 Diameter (in) 8-1/2
TP2163060 Kerf (mm) 2.0
TP2163060 Rake 5
TP2163060 RPM 7000
TP2163060 Teeth 60
TP2163060 Tooth Grind ATB 15

Even if someone could point me in the right direction would be much appreciated.

 

[SkipVought]

Hi,

Your beginning table is what could be referred to as a report. Reports are kind of useless for performing analysis in Excel. What you need is normalized data.

The Excel Pivot Table Wizard can be a tool to help you normalize your data.

faq68-7103

You can start the Pivot Table Wizard via alt+d+p and continue with the instructions in the linked FAQ.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Aside from Skip's suggestion, you can also use a simple macro with a few lines of VBA code to transform what you have to what you need.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Another method: excel pover query from excel table has explicit unpivot action.

combo

 

[dhookom]

I've used Skip's suggestion in Excel with great success. Being a database guy, I would import as is and then create a union query that can be used as the source for a make table query:

SELECT Code, "Bore (mm)" AS Characteristic, Bore
FROM SawBlades
UNION ALL
SELECT Code, "Bush (mm)", Bush
FROM SawBlades
UNION ALL
SELECT Code, "Diameter (mm)", DiameterMM
FROM SawBlades
UNION ALL
SELECT Code, "Diameter (in)", DiameterIN
FROM SawBlades
UNION ALL
SELECT Code, "Kerf (mm)", Kerf
FROM SawBlades
UNION ALL
SELECT Code, "Rake", Rake
FROM SawBlades
UNION ALL
SELECT Code, "RPM", RPM
FROM SawBlades
UNION ALL
SELECT Code, "Teeth", Teeth
FROM SawBlades
UNION ALL
SELECT Code, "Tooth Grind", [Tooth Grind]
FROM SawBlades
ORDER BY 1;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Had a few minutes to kill...
If your data is in columns A-J with the header in row 1, my output is in columns M-N-O starts in row 1

Option Explicit

Sub [blue]blackduck[/blue]()
Dim R As Integer
Dim C As Integer
Dim R_Out As Integer

R = 2
R_Out = 1

Do While Cells(R, 1).Value <> ""
    For C = 2 To 10
        Range("M" & R_Out & ":O" & R_Out).Value = Array(Cells(R, 1), Cells(1, C), Cells(R, C))
        R_Out = R_Out + 1
    Next C
    R = R + 1
Loop

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[blackduck]

Thank you soo much for everyone's help, works a treat!
I have used Andy's code because the names and number of my column headings will vary between different ranges of products.
The code below is Andy's with an extra line I have added to handle the variable number of columns.
(ps. Haven't been on the forum for a long time, but heartwarming to see my old helpers Skip and Duane still pop up straight away offering great solutions and advice.)

Option Explicit

Sub Attributes()
Dim R As Integer
Dim C As Integer
Dim R_Out As Integer
Dim lastCol As Long

R = 2
R_Out = 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Do While Cells(R, 1).Value <> ""
For C = 2 To lastCol
Range("M" & R_Out & ":O" & R_Out).Value = Array(Cells(R, 1), Cells(1, C), Cells(R, C))
R_Out = R_Out + 1
Next C
R = R + 1
Loop

End Sub

 

[Andrzejek]

You have also changed the name of the Sub, not fair...
Your [tt]lastCol[/tt] is fine, as long as it does not overlap column M.
It would be safer to create the output on another Worksheet, something like:

...
[blue]Sheets([highlight #FCE94F]2[/highlight]).[/blue]Range("[RED]A[/RED]" & R_Out & ":[RED]C[/RED]" & R_Out).Value = _
Array([blue]Sheets(1).[/blue]Cells(R, 1), [blue]Sheets(1).[/blue]Cells(1, C), [blue]Sheets(1).[/blue]Cells(R, C))
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson