Dynamically Transposing and Deleting Rows to Columns

**newB55** · 09-19-2016, 02:28 AM

Hello,

I'm really new to the world of VBA, and would appreciate some help. I have data that needs to be transposed from rows to columns within the same spreadsheet.(the rows that are transposed can then be deleted. ) The ranges would need to be set dynamically because the number of rows will vary, but each row is associated with unique id in row E - that's repeated, except for the header row. Only 4 columns will actually need to be repeated, and each inserted group of 4 (how many groups of 4 will depend on row count for each unique id in column E). These groups will need to be inserted BEFORE the last 3 columns.

I spent a lot of time trying to wrap my head around this, and just not getting anywhere. Please help!

**mike7952** · 09-19-2016, 02:39 AM

Best to upload an excel workbook, hard to work with pictures. Of course if data is sensitive replace with mock up data

**newB55** · 09-19-2016, 06:43 AM

Thanks Mike7952. Here is the excel workbook.

Also including some code that I was working on, but its resulting in errors at the moment

I think I just need serious help on how to:

1. InputRng that is comprised of selected columns - not all of the columns E though S - i really need column E, P, Q, R S.
2. OutRng that would be column before the last 3 columns
3. How keep iterating (while unique id in column E is the same) and transposing the associated rows for columns P - S only . (except for that 1st repeating group for each unique id, which is already transposed technically )
4. How to also ensure the header row for the P-S should also be repeating.
5. How to delete those repeating rows after transposing

Please Login or Register  to view this content.

'this is not working since i don't want all of the columns between E and S..just some
Set InputRng = Range("E1", Range("S1").End(xlDown))

' not working either...but it should be inserting it before Column T

Please Login or Register  to view this content.

**mike7952** · 09-19-2016, 02:26 PM

Give this a try

Please Login or Register  to view this content.

**newB55** · 09-19-2016, 05:15 PM

This is great! This was so hard for me. Here some things that need tweaking...

1. If I try to have one unique id with say 8 rows... the the last 3 rows somehow end up in the middle and not at end. They need to always be last, and i will never know what the count would be ...4, 8, 10. (example: See "new input tab" for unique id 1114, and see the output in "sheet1" - AF, AG, AH should always be at the end of each row the last 3 columns.)

2. Is it possible to have the the header row remain intact plus the columns should keep repeating for P, Q, R, S , like in "sample" tab - Header p Header q Header r Header s Header p Header q Header r Header s. Can all the cells also keep their original formatting?

3. Some of the cells (in "new input" tab in red) have formulas, so when i ran the macro, they no longer worked. (T2, U2, V2 & T10, U10, V10 and T12, U12, V12). Any way to automate that as well so it carries over the formula given the new row vs column structure)

**mike7952** · 09-19-2016, 06:16 PM

Will have to look at tomorrow I'm at work now for 12 hours.

Thanks Mike

**newB55** · 09-20-2016, 09:09 AM

Thanks mike7952!!!! I appreciate your help.

**newB55** · 10-02-2016, 02:17 PM

Hi mike7952. I tried to fix the function but getting stuck on the portion below. It looks like when the 1st column contains duplicates (which could happen) then the last 3 columns (20, 21, 22) of the row with duplicate, get repeated and don't reflect the actual values in 20, 21, 22 for that row. Also, how can I dynamically set the column destination (right now is its 32) - i just want the destination column (for 20, 21, 22 ) to be the next cell in that row, after last 4 column grouping.

Also, how to do I get the header row to also repeat but only for P through S. Thanks

For Each w In .Items
Cells(iCounter, 1).Resize(, UBound(w) + 1) = w
With WorksheetFunction
'w(0)
iRow = .Match(w(0), .Index(arr, 0, 1), 0)
'32
' last 3 summary columns
Cells(iCounter, 32).Resize(, 3).Value = Array(arr(iRow, 20), arr(iRow, 21), arr(iRow, 22))

Dynamically Transposing and Deleting Rows to Columns

LinkBack

Thread Tools

Rate This Thread

Display

Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Re: Dynamically Transposing and Deleting Rows to Columns

Thread Information

Users Browsing this Thread

Similar Threads

Need help transposing rows into columns

[SOLVED] Transposing 2 - 70,000+ long columns into approx 4600 rows with 15 columns each

[SOLVED] Need help with transposing columns to rows

macro help for transposing data and deleting rows

Transposing data from rows into columns (columns have been merged)

Transposing columns to rows

Transposing columns and rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions