Transpose Based on Criteria

[Neon520]

Hi Everyone,

I'm dealing with an odd data set here that need to be transpose some how,
here is the sample of the data set:

Col A | Col B | Col C | Col D | Col E | Col F |
Col G |
Name | Address | ID# | Line Item # | Item Des | Other1 | Other2 |
John | 123 street | 147 | 1 | s.thing1 | note1.1 |
note1.2 |
(blank)| (blank)| (blank)| 2 | s.thing2 | note2.1 |
note2.2 |
(blank)| (blank)| (blank)| 3 | s.thing3 | note3.1 |
note3.2 |
(blank)| (blank)| (blank)| 4 | s.thing4 | note4.1 |
note4.2 |
SMITH | 369 street | 148 | 1 | s.thing1 | note1.1 |
note1.2 |
(blank)| (blank)| (blank) | 2 | s.thing2 | note2.1 |
note2.2 |
(blank)| (blank)| (blank) | 3 | s.thing3 | note3.1 |
note3.2 |


What I need to do is to get rid of all the blank cell and Transpose the Col
D and E (Line Item # and Item Des) to be in a column format instead.

That problem:
Line item # and Item Des are varies from name to name.
There are Col F and Col G (more than 2 columns) following the Line Item #
and Item Des, which will be overwrite after transpose, if not done correctly.

Does anyone have a solution to this without having to Copy > Paste Special
Transpose the data Name by Name Manually? I'm talking about thousands of
record here.

Thank you for any advice or help.

Neon520

 

[OssieMac]

I am not sure that I understand exactly what the output needs to look like.
Even though the source data has broken up somewhat in this posting I believe
that I have established what it should look like. That are several rows for
each record and each record is identified by Name, Address and ID# and the
following rows with blanks for Name, Address and ID# belong to that same
record. Is this corect?

Am I correct in my understanding that you want the Line item # and Item Des
to be across the page on the same row as the Name, Address and ID# so there
is only one row per record? If so, what happens to Other1, Other2 etc; do
they have to be transposed to the one row also?

If my understanding is not correct then can you post an example of what you
want the output to look like for the first record.

Your quote: "There are Col F and Col G (more than 2 columns) following the
Line Item # and Item Des, which will be overwrite after transpose, if not
done correctly."
Not too hard to overcome. Can just look for the max (highest number) in the
Line Item# and insert/allow sufficient blank columns.

I suggest that the output be to a separate new worksheet. My reason is to
leave the source untouched so that you can compare source to output to
confirm that is what you want.

 

[Neon520]

First part, yes you are correct.

Other1, Other2 etc are not going to be transposed. They are going to be
pushed back after the Line Item # and Des being transposed.

Yes, you are correct about using the max of Line Item # for transposing and
leaving the other records that have lesser Line Item # blank (see ex below).

Another problem that I can think of right now that I didn't mention the
first time around is row 2, 3 ... underneath Name, Address, ID# are not
necessary blank. But since you're going to transfer the transposed data to a
new sheet, the extra info can simply be ignored, right.

Here is the same data of what it should look like:

Col A | Col B | Col C | Col D | Col E | Col F | Col G |
Col H | Col I |
Name | Address | ID# | Line Item1 | Line Item2 | Line Item3 | Line Item4 |
Other1| Other2 |
John | 123 street | 147 | s.thing1 | s.thing2 | s.thing3 |
s.thing4 | note1.1 | note1.2 |
SMITH | 369 street | 148 | s.thing1 | s.thing2 | s.thing3 | (blank) |
note1.1 | note1.2 |


Note that since we transpose Line Item # to become Header Row and the Item
Des to be the correspond Item Line # for each record. Also note that Note2.1,
Note2.2, Note3.1, Note3.2 (and maybe the blank cell underneath Name, Address,
ID etc,) for each record need NOT to be transfer to the NEW Transposed Sheet.

If possible, can you make the code so that I can identify the Line Item
Number and Item Des? I really like the way you set up for the previous post
for deleting by selecting the color. I'm working a huge amount of records
and in a NON-uniform way, so the code might work in one sheet but not the
other, since the column got moved around.

Thank you for your response to my desperate request.
Regards,
Neon520

 

[OssieMac]

Hi again,

Your question "If possible, can you make the code so that I can identify the
Line Item
Number and Item Des"

Getting too complex expecially without the actual worksheets to test on. I
think that it might be better if you simply use cut and insert with the
columns so that they are in the correct order as per the sample source and
output data that you gave me.

Anyway try the following and see if it does what you want. It will only work
with the source data set out identical to the sample you gave me. however, it
does identify the maximum Line Item # and creates sufficient columns for the
data.

You will need to edit the code to insert your worksheet names for the source
data and the output. See the comments in the code.

Sub RearrangeData()

Dim wsSource As Worksheet
Dim wsOutput As Worksheet
Dim rngItem As Range
Dim lngMaxItem As Long
Dim i As Long
Dim c As Range

'Edit "Sheet1" to match sheet name containing source data
Set wsSource = Sheets("Sheet1")

'Edit "Sheet2" to match sheet name for output
'NOTE: You will loose any previously existing data on output sheet.
Set wsOutput = Sheets("Sheet2")

wsOutput.Cells.Clear 'Clears Output worksheet

With wsSource
Set rngItem = .Range(.Cells(2, "D"), _
.Cells(.Rows.Count, "D").End(xlUp))
End With

'Finds the Max number of lines for any record
lngMaxItem = WorksheetFunction.Max(rngItem)

With wsOutput
'Inserts column headers on Output worksheet
.Cells(1, 1) = "Name"
.Cells(1, 2) = "Address"
.Cells(1, 3) = "ID#"

'Creates Line Item headers for Max number
'of lines per record
For i = 1 To lngMaxItem
.Cells(1, i + 3) = "Line Item" & i
Next i

'Creates Other headers after Line Items
.Cells(1, i + 3) = "Other1"
.Cells(1, i + 4) = "Other2"

End With

For Each c In rngItem
If c = 1 Then 'First row of each record
wsOutput.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0) = c.Offset(0, -3)

wsOutput.Cells(Rows.Count, 1).End(xlUp) _
.Offset(0, 1) = c.Offset(0, -2)

wsOutput.Cells(Rows.Count, 1).End(xlUp) _
.Offset(0, 2) = c.Offset(0, -1)

wsOutput.Cells(Rows.Count, 1).End(xlUp) _
.Offset(0, 3) = c.Offset(0, 1)

wsOutput.Cells(Rows.Count, 1).End(xlUp) _
.Offset(0, i + 2) = c.Offset(0, 2)

wsOutput.Cells(Rows.Count, 1).End(xlUp) _
.Offset(0, i + 3) = c.Offset(0, 3)
Else
'Subsequent rows of each record
If c > c.Offset(-1, 0) Then
wsOutput.Cells(Rows.Count, 4).End(xlUp) _
.Offset(0, c - 1) = c.Offset(0, 1)
End If
End If

Next c

'Format column headers Bold and AutoFit column widths
With wsOutput
.Rows("1:1").Font.Bold = True

.Range(Columns(1), _
Columns(.UsedRange.Columns.Count)).AutoFit
End With

End Sub

 

[Neon520]

Hi OssieMac,

I got an error message 13: Type mismatch in the code below.
If c = 1 Then 'First row of each record

What do I need to do if there are more than 3 columns (Name, Address, ID#)
in front of Line Items #? I see that you set the Line Items # in column D,
can I just change that according to my line item #?

what about if there are more than 2 columns (Other1, Other2)? If this is
easier to modify, I can just cut however many columns exceeding the 3 columns
to the end of the worksheet.

Thank you,
Neon520

 

[Neon520]

Hi OssieMac,

I forgot to tell you that I'm using Office 2004 for Mac.

And now I tried your code again on a Window Machine using Office 2007.

It seems to transfer the data to Sheet2 fine.

But here a different error code I get:
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

For the code below:
..Range(Columns(1), _
Columns(.UsedRange.Columns.Count)).AutoFit

After I exclude that from the code, it works fine. I figure it's not such a
big issue for AutoFit, right?

One other strange output the I notice is that somehow Excel didn't pick up
the Maximum of Line Items #. The largest number in the Line Item # column is
14, but after running the code there is only 11. I know it is not your code
problem, because when I do a formula =MAX(Line Item Col), it also give me 11.
However, I don't quite figure out why it pick up only 11 instead of 14.

The original was exported from some type of database software that the Line
Item # was stored as Text, and I tried to Format the column as Number to see
if it picks 14, but it still stay as Text. Any idea?

Thank you a lot.
Neon520

 

[OssieMac]

I got an error message 13. The error indicates that it is not the correct
column. the code asuumes that the Line Item # is in the 4th column (D).

My interpretation of your previous postings were that the original data
looks line the following example:-

Name Address ID# Line Item # Item Des Other1 Other2
John 123 street 147 1 s.thing1 note1.1 note1.2
2 s.thing2 note2.1
note2.2
3 s.thing3 note3.1
note3.2
4 s.thing4 note4.1
note3.2

As per my previous post, it is too complex to make the code generic for data
that is not set out in a standard format; especially when I do not have the
various worksheets for test.

 

[OssieMac]

Format the problem column to number.

Pick a blank cell on the worksheet and enter 1 in the cell.

Copy the cell

Select the data in the problem column and paste special-> multiply.

Test with Max formula. If does not work then try following:-

Insert a helper column beside the problem column.

Format the helper column as number

Enter the following formula (replace D2 with your column number):-

=VALUE(TRIM(CLEAN(D2)))

Copy formula down for full length of data

Select the column, copy and paste special-> values over top of itself.

Test the temporary column with Max function.

If it works then delete the original column and use the new column.

If it does not work then I don't know the answer.

 

[Neon520]

Hi OssieMac,

Thanks for your rapid response.

I think you miss a couple of my previous questions.

Let me repost it here:

What do I need to do if there are more than 3 columns (Name, Address, ID#)
in front of Line Items #? I see that you set the Line Items # in column D,
can I just change that according to my line item #?

what about if there are more than 2 columns (Other1, Other2) After Items
Des? What do I need to do so that Other3, Other4, etc... will appear after
Other2 so on and so forth.

If this adding column at the end of the new sheet is easier than trying to
add them before the Line Item # and Item Des, I can just cut however many
columns exceeding the 3 columns (Name, Address, ID) to the end of the
worksheet and move them back once they got transferred to the new sheet.

Thank you,
Neon520

 

[OssieMac]

I thought that I did answer your questions in a previous post but perhaps I
did not make it clear. I can't really help you any more if the worksheets do
not match the layout that you provided. I am not able to make it generic
without copies of all of the types of layouts for testing. Therefore if they
match the sample you provided then I believe that the code works otherwise it
won't.

Your question: "I see that you set the Line Items # in column D, can I just
change that according to my line item #?" The answer is No because it will
upset the positioning of all the other values.