#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Here you go!
(Columns RowNo and CustRow in Tables are meant to be set hidden.) |
#3
|
|||
|
|||
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!! |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
Quote:
Same question but if I want to transpose vertical by groups of 3.... I have attached an xlsx for example Thank you! |
#6
|
|||
|
|||
An easy one
|
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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),"") |
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 |
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 |
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 |