Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-24-2020, 01:02 AM
Alex1s85 Alex1s85 is offline Transpose data form columns in rows matched with a key Windows 10 Transpose data form columns in rows matched with a key Office 2016
Advanced Beginner
Transpose data form columns in rows matched with a key
 
Join Date: Oct 2019
Location: Thessaloniki, Greece
Posts: 30
Alex1s85 is on a distinguished road
Default Transpose data form columns in rows matched with a key

Hello to every one,


I have a table with thousands of records(rows) with data in 3 columns.


In the first column I have the customer id and in the other columns their invoices numbers and the date (may the customer id appears more than one times).


I want to transpose the invoice numbers and match them with the customer id horizontally.

For example, I want to have a transformation as in the picture.

I 've tried with PQ but w/o successful results.
Attached Images
File Type: png dfghj.png (10.1 KB, 25 views)
Reply With Quote
  #2  
Old 11-24-2020, 02:41 AM
ArviLaanemets ArviLaanemets is offline Transpose data form columns in rows matched with a key Windows 8 Transpose data form columns in rows matched with a key Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Here you go!

(Columns RowNo and CustRow in Tables are meant to be set hidden.)
Attached Files
File Type: xlsx TransposeData.xlsx (16.9 KB, 16 views)
Reply With Quote
  #3  
Old 11-24-2020, 03:42 AM
Alex1s85 Alex1s85 is offline Transpose data form columns in rows matched with a key Windows 10 Transpose data form columns in rows matched with a key Office 2016
Advanced Beginner
Transpose data form columns in rows matched with a key
 
Join Date: Oct 2019
Location: Thessaloniki, Greece
Posts: 30
Alex1s85 is on a distinguished road
Default

WOW!!

That's very very impressive!!

I don't know if I am capable to implement such a complicated way, but I will definitely try.

Thank you very much!!
Reply With Quote
  #4  
Old 11-24-2020, 06:37 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Transpose data form columns in rows matched with a key Windows 7 64bit Transpose data form columns in rows matched with a key Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

In the future please post a sample sheet instead of a nice but useless picture so that we can concentrate on helping instead of retyping.
Thanks
Reply With Quote
  #5  
Old 11-25-2020, 06:28 AM
Urraco Urraco is offline Transpose data form columns in rows matched with a key Windows 8 Transpose data form columns in rows matched with a key Office 2016
Advanced Beginner
 
Join Date: Apr 2018
Posts: 30
Urraco is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Here you go!

(Columns RowNo and CustRow in Tables are meant to be set hidden.)
Hello everybody

Same question but if I want to transpose vertical by groups of 3....
I have attached an xlsx for example
Thank you!
Attached Images
File Type: jpg transpose verticaly.jpg (39.7 KB, 21 views)
Attached Files
File Type: xlsx transpose vertical .xlsx (9.7 KB, 9 views)
Reply With Quote
  #6  
Old 11-25-2020, 07:11 AM
ArviLaanemets ArviLaanemets is offline Transpose data form columns in rows matched with a key Windows 8 Transpose data form columns in rows matched with a key Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

An easy one
Attached Files
File Type: xlsx transpose vertical .xlsx (12.3 KB, 14 views)
Reply With Quote
  #7  
Old 11-25-2020, 11:51 PM
Urraco Urraco is offline Transpose data form columns in rows matched with a key Windows 8 Transpose data form columns in rows matched with a key Office 2016
Advanced Beginner
 
Join Date: Apr 2018
Posts: 30
Urraco is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
An easy one
Thanks for help!
Your formula works but not for what i need...
I have attached an excel in which I have a formula for horizontal transposition.

=OFFSET($A$2,COLUMNS($A2:A2)-1+(ROWS($2:2)-1)*24,0)

if it's possible, based on this formula can you make it work vertically, please? In groups of 4, 10,15, etc ... as in the example in excel.
Attached Files
File Type: xlsx transpose vertical (2).xlsx (10.8 KB, 9 views)
Reply With Quote
  #8  
Old 11-26-2020, 01:34 AM
ArviLaanemets ArviLaanemets is offline Transpose data form columns in rows matched with a key Windows 8 Transpose data form columns in rows matched with a key Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

As it seems, you don't like to use defined Tables, so I used regular worksheet formula now instead Table formula. I also modified formula, so it is same for all columns now.

But be aware, you have to redesign formula whenever you redesign your tables (add new entries into column A, add rows at top of your tables, add new columns into your tables or between them, etc. Unlike Table formulas, regular ones don't adjust automatically.

Edit: To make the formula easier to understand, for cell K2 you may have it as
Code:
=IFERROR(INDEX($A$2:$A$33,(ROW()-1) +INT(((ROW()-1)-1)/4)*4*(4-1) + (COLUMN()-11)*4),"")
which can be explained as
=IFERROR(INDEX(SourceColumnRange,(SheetRowNumber-NumberOfHeaderRows) +INT(((SheetRowNumber-NumberOfHeaderRows)-1)/NumberOfTargetColumns)*NumberOfTargetColumns*(NumberOfTargetColumns-1) + (SheetColumnNumber-NumberOf1stTargetColumn)*NumberOfTargetColumns),"")
Attached Files
File Type: xlsx transpose vertical (2).xlsx (11.0 KB, 8 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging all data between two sheets into a third one without duplicates or rows or columns Ricko_uk Excel 4 08-27-2020 01:16 AM
Transpose multiple data form rows in columns matched with a key Alex1s85 Excel 6 06-24-2020 07:08 AM
Transpose data form columns in rows matched with a key Mail Merge with Fields extracted from ROWS instead of COLUMNS or transpose of typical mail merge djrexy Mail Merge 1 05-20-2020 04:04 PM
Transpose data form columns in rows matched with a key How to Loop through rows and Columns to Offset Data Josh1012 Excel Programming 4 09-06-2018 03:20 AM
Insert rows when column A data changes then shade and autosum blanks in columns E to I kgoosen Excel Programming 0 08-30-2017 04:45 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft