Horizontal list of IDs & data needs to be vertically stacked in their own individual row

[kdm]

I receive this report each week. Currently the report contains roughly 95,000 rows of data (I anticipate this to increase over time).
END RESULT: I need to have directions and/or an understanding on how be able change the layout of this report each week to the new layout.


Original report sample of 15 rows (out of 95,000 rows)
ID's are listed in the columns (A:T) with the applicable related data listed in columns (U:AF) for these ID’s.
[B]See attachment for picture of original file layout.


New Layout needed:
I need each ID to be moved from columns B:T and stacked vertically in column A with the related data from the original file in columns (U:AF). Therefore, each ID needs to:
• move from columns B:T to column A and have its own individual row
• have a copy of the related information from the original rows U:AF to be placed in the new row columns U:AF
• then columns B:T (IDs have been moved to column A and are no longer needed) should be deleted
• finally the related data should end up in columns B:M
[B]See attachment for picture of Final file layout.
Please remember the file is currently 95,000 rows.


Original report attached with 41 ID’s listed in A2:T16 (and related information in U2:AF16):
EverTrue - original Interactions file for Friday reports.png

Final report attached with 41 ID’s stacked on top of each other in rows A2:A42 in (and related information now in B2:M42):
EverTrue - Final Interactions file layout for Friday reports.png

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[dflak]

Also, if you can, post pictures in JPEG format. Some of us can't see PNG files on this board.

[kdm]

Reports in Excel file now posted - please look at the bottom of this post.
1) Original report (how we receive the data - sample of 15 rows out of 95,000 rows)
2) Final Layout (Mock up of how we need the data to look - same sample of 15 rows of data out of the 95,000 rows)

Reports in JPG image now posted.
1) Original report (how we receive the data - sample of 15 rows out of 95,000 rows)
2) Final Layout (Mock up of how we need the data to look - same sample of 15 rows of data out of the 95,000 rows)

[kdm]

Hello AliGW - I have updated the post with the excel files you requested.

[kdm]

Hello dflak,

I have updated the post with the image files you have requested. Thank you.

[dflak]

It looks like all you need is to remove some columns from the initial data. Here is a spreadsheet that works "outside" of both the source and destination workbooks. Fill in the source and destination path names and file names in the indicated places. Then click on the copy report button.

What it does is open the source file, remove columns B:T and saves it under the new name.

[newdoverman]

You could use GROUP found in the OUTLINE feature of Excel to hide/unhide columns B:T inclusive. Notice the small 1 and 2 in the upper left of the picture. Click on the 1 and the columns B:T are hidden. Click on the 2 and all columns are visible.Group Capture.JPG

[kdm]

I need all ID's from rows B:T to be moved to their own individual row in column A AND to pull over with the related data in columns U:AF, as displayed in the pics. If you know how to do this, I would love to hear back from you.

[AliGW]

You have not responded to the suggestions made in posts #7 and #8 - it would be useful to know in what way they are not working for you.

[newdoverman]

I can't understand how your data would look. Row 6 for example has 15 IDs and row 7 has 10 IDs.

As it is right now, your requirements don't make sense to me.

[kdm]

I need all ID's from rows B:T to be moved to their own individual row in column A AND to pull over with the related data in columns U:AF, as displayed in the pics. If you know how to do this, I would love to hear back from you.EverTrue - original Interactions file for Friday reports - another example.jpg

[AliGW]

You said this in post #9. Please look at the comments and suggestions made already and respond to each of them if you want help with this. Clearly members are not understanding what you are wanting. Thanks.

[kdm]

Hello - Posts # 7 & 8 do not work as they skipping the first step and moving to the last step. The suggestions in #7 & 8 give a way only remove or hide columns B:T. We need to move the data from B:T into A. AND when we move that data into A, we need to retain the information in cells U:AF in the new row created.
EverTrue - original Interactions file for Friday reports - another example.jpg

[kdm]

Can you see the pictures or the files I attached? Here is another picture that may help. In the end, this change needs to occur to thousands of rows of data, not just the sample 15 rows.
EverTrue - original Interactions file for Friday reports - another example.jpg

[kdm]

Here is another example of how I would need the rows to change. Please let me know if you need any other examples...

Original file and Mock up file.jpg

[AliGW]

Please provide an annotated workbook. The images you have posted are not fit for purpose: the text on them is far too tiny to read!

[kdm]

Please see the attached files as requested. These are the same as the Excel files in the beginning of this thread except I have added colors to help identify how the file changes from original to mock up.
Please let me know if you need anything else.

[newdoverman]

Here is a possible solution.
On a separate worksheet I entered this formula in A2 and filled down to convert the columns A:T to rows. This includes 0s for blank cells

Please Login or Register  to view this content.

In B2 of the separate worksheet I entered this formula with Ctrl + Shift +Enter and filled down. This is an Array Formula.

Please Login or Register  to view this content.

Enter in C3 this Array Formula. Enter with Ctrl + Shift + Enter. This will give the cell addresses of the values in column B.

Please Login or Register  to view this content.

In D2 enter this formula and fill down and across to extract the data from the 'Original 1st 15 rows' worksheet.

Please Login or Register  to view this content.

Format the results to properly display the data as you want. Column E format as dates and widen column K so that the result isn't displayed as scientific notation. Other columns require word wrap and auto fit row height.

[newdoverman]

The first formula in message 19 can be slightly shortened to the following. Enter in Sheet1!A2 and fill down.

Please Login or Register  to view this content.

[kdm]

Hello NewDoverMan,

I tried the directions you gave an I did not get the result we are looking for... maybe you can see why? Here is what I received:
NewDoverMan.jpg

[newdoverman]

You really changed things by addressing a separate file. You also didn't enter the Array formulae with Ctrl + Shift + Enter.
This ZIP file has both files linked together and it works. I have copied the formulae down to row 100 for the finished results. The formula in column A extends down to row 600 in case you add more data.
Extract the files to the same folder. Newdoverman Results.xlsx is the file with the formulae. The other file is the data source.
Both files must be open at the same time if the links are to work.

[kdm]

Good morning,

Sorry for the delay in the reply. This is really close - we are almost there. I'm was very excited when I reviewed the file you sent this morning. Could you please look into the following concern?

The issue will be in the column heading of U80.ET.CONTACT.SUMMARY. For some reason only some of the data was placed in this column and some was not. I have highlighted in yellow some of the cells that should have content and the related CONTACT.PROSPECT.ID_ numbers for those comments. If we can get this resolved we will have the process nailed down. Then the only thing left if for me to be able to recreate it for the complete file and not just the sample. Thank you very much

Sample 15 rows - Interactions.jpg

[newdoverman]

I made a very small mistake in the formula with a large error as the result. In the NewDoverMan results.xlsx workbook Sheet1 cell D3 add a $ sign in front of the C in the formula like this =IF($C3 then fill the formula across and down or down and across (whichever you like).
The formula should be:

Please Login or Register  to view this content.

I added dummy data to the Copy of the Original 15... workbook and the values were copied over perfectly.

[newdoverman]

This zipped file has the results I referred to in my previous message.