Excel VBA Transpose Problem

[wbow]

Hi I have a spreadsheet of 9,000 rows by 25 columns and I need to transpose some of the data as follows, can anyone suggest a solution, other than manually transposing?

In all columns row 1 is column header, all data from row 2 onwards.
Column A has Part Number
Column B has a Count of locations where each part is used
Column C ready to receive Used copied data
Columns D to S has names of locations where each part is used.

I need to get the value from column b and where this is >1, insert the corresponding number of rows BELOW it And copy the values from cells A and B into cells A and B in the newly created rows.
Next scan along the row and where a cell in not empty, copy the value into column C, repeating this until reaching the cell in column S, incrementing one row down column C for each entry found.

What I have;
Part Count Used 100 – 200 – 300 – 400 [so on for 16 columns]
FX12 1 100 - Blank to end of row
FX14 2 100 - Blank-300-Blank to end of row
GL23 4 100 - 200 - 300 BLANK Cells until-800-Blank to end of row

What I need;
Part Count Used 100 – 200 – 300 – 400 [so on for 16 columns]
FX12 1 100
FX14 2 100
FX14 2 300
GL23 4 100
GL23 4 200
GL23 4 300
GL23 4 800

Many thanks,

 

[SkipVought]

hi,

When I read, "Columns D to S has names of locations where each part is used" I said to myself, "Self, this PROBABLY is where the issue resides.

What you have is non-normalized data, that you need to normalize.

1. So will this be an redundant effort, that is will you be doing this normalization of data periodically as time goes on?

2. Is there some data source from which this data report is derived? If so, you may be better off deriving a solution from that source than from your report?

What be your answer to these 2 questions?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Please also post a representative sample of your data here.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I assume this is what you have to start with:

[pre]
Part Count Used 100 200 300 400 500 600 700 800 900 ...
FX12 1 100
FX14 2 100 300
GL23 4 100 200 300 800
[/pre]
It is easier to see if you format it right.

Have fun.

---- Andy

 

[wbow]

Hi Andrzejek,
Apologies for the poor formatting, but yes what you posted is what I have.

 

[SkipVought]

answers to question???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

Hi SkipVought,
This is the data normalisation process as the source system is precluded from extract report development or enhancement due to it's impending demise.

Additionally, the knowledge gained will be useful elsewhere and help develop my meagre VBA knowledge.

I have tried to attach a sample, but our system security appears to be preventing me from doing so.

Many thanks for your interest.

 

[SkipVought]

You might try this process based on faq68-5287:

Select column D
INSERT column
Enter this formula in D1 and copy down:
[tt]
D1: =A1&"|"&B1
[/tt]

Activate the PivotTable Wizard: alt+D P > Select MULTIPLE consolidation ranges/PivotTable/NEXT > Range, Select all the dat in columns D:T and ADD/NEXT > FINISH

On the PivotTable Field List: Drag the ROW LABELS off and drag the COLUMN LABELS off.

In the PivotTable, double-click the Count of Values VALUE.

This takes you to a new sheet containing the basis for your new table.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

Hi SkipVought,
I had tried using pivot tables but not that particular approach and whilst it worked perfectly it basically puts me where I originally started.
I still need to insert rows and copy the actual usage number etc, hence I discounted the pivot table route.

Again, many thanks.

 

[SkipVought]

after deleting BLANK values, this is the result from my process...

[pre]
Row Column Value Page1
FX12|1 100 100 Item1
FX14|2 100 100 Item1
FX14|2 300 300 Item1
GL23|4 100 100 Item1
GL23|4 200 200 Item1
GL23|4 300 300 Item1
GL23|4 800 800 Item1
[/pre]

It obviously needs a bit more manipulation, but the result is NOT "where I originally started!"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Not the most 'elegant' way, but it works:

Dim r As Integer
Dim c As Integer

r = 2   [green]'Data starts in row 2[/green]

Do While Range("A" & r).Value <> ""
    For c = 3 To 18[green]
        'Loop thru cells C (3) to R (18)[/green]
        If Cells(r, c) <> "" Then
            Debug.Print Range("A" & r).Value & " " & Range("B" & r).Value & " " & Cells(r, c)
        End If
    Next c
    
    r = r + 1
Loop

You get:
[tt]
FX12 1 100
FX14 2 100
FX14 2 300
GL23 4 100
GL23 4 200
GL23 4 300
GL23 4 800
[/tt]

Have fun.

---- Andy

 

[wbow]

Hi Andy,
Inserted your code into VBA as a module;
Option Explicit
Sub GetData()
Dim r As Integer
Dim c As Integer

r = 2 'Data starts in row 2

Do While Range("A" & r).Value <> ""
For c = 3 To 18
'Loop thru cells C (3) to R (18)
If Cells(r, c) <> "" Then
Debug.Print Range("A" & r).Value & " " & Range("B" & r).Value & " " & Cells(r, c)
End If
Next c

r = r + 1
Loop
End Sub
Ran the Macro and nothing happened, what am I doing wrong?

Many thanks.

 

[wbow]

Hi SkipVought,
Tried your solution again and it worked like a dream. Just a little tidying up to do.

Many thanks again.

 

[Andrzejek]

Did you step thru the code?
You should get the output in the Immediate Window with the Debug.Print line of code.
You will not see anything in the sheet, so that is probably what you ment by "nothing happened"

Have fun.

---- Andy

 

[wbow]

Hi Andrzejek,
Yep that's what I did..... I presume that to get it to work I comment out the
Debug.Print Range("A" & r).Value & " " & Range("B" & r).Value & " " & Cells(r, c)
line and substitute something else?

Many thanks,

 

[Andrzejek]

Yes, you need to change the Debug.Print line to something else.
Yo never stated (as far I can tell) where you want to have your output, that's why I gave you the logic, you just need to decide where to put the outcome.
You may write it into a text file, or place it on Sheet2, or whatever.

Have fun.

---- Andy

 

[wbow]

Hi Andrzejek,
Apologies, I did not make myself clear in my original post.

Initially I though about finding the first row a part number appears in and then
using the COUNT column value to insert that number of rows below the first
instance of the part number, [copying the part number into the blank cells created],
then transpose the values found in the USED colums into the VALUE column.

However, if you could tell me how to output your code to a new sheet, then that would
probably be wiser.

Many thanks.

 

[Andrzejek]

If you have your data in Sheet1, and you want your output in Sheet2 starting in A1-C1 down:

Dim r As Integer
Dim c As Integer[blue]
Dim r2 As Integer[/blue]

r = 2   [green]'Data starts in row 2[/green][blue]
r2 = 1[/blue]

Do While [blue]Sheet1.[/blue]Range("A" & r).Value <> ""
    For c = 3 To 18[green]
        'Loop thru cells C (3) to R (18)[/green]
        If [blue]Sheet1.[/blue]Cells(r, c) <> "" Then
            [red]Sheet2.[/red]Range("A" & r2 & ":C" & r2).Value = Array([blue]Sheet1.[/blue]Range("A" & r).Value, [blue]Sheet1.[/blue]Range("B" & r).Value, [blue]Sheet1.[/blue]Cells(r, c))[blue]
            r2 = r2 + 1[/blue]
        End If
    Next c
    
    r = r + 1
Loop

Have fun.

---- Andy

 

[wbow]

Hi Andrzejek,
Many thanks for the post, will try this out.