transpose data

ExcelNewbie2020

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
350
Office Version
  1. 365
Platform
  1. Windows
Sirs,

I have data arrange horizontally.. would it be possible to spill the data vertically from cell G25 of a selected name in cell G22.. Many thanks

Book2
ABCDEFGHIJKLMNOPQRST
1
2PAYMENT
3NAMEAMOUNTDATEDATEAMOUNTDATEAMOUNTDATEAMOUNTDATEAMOUNTDATEAMOUNTDATEAMOUNTDATEAMOUNTDATEAMOUNT
4NAME130010-Jan-202411-Sep-20243720-Dec-20241023-Sep-20248819-Apr-202478
5NAME24007-Jan-20246-Oct-20243327-Oct-20244627-Mar-2024717-Dec-20241918-Nov-2024963-Sep-20248626-Oct-20247516-Mar-202464
6NAME350027-Jan-20245-Aug-20243410-Jun-20245315-Aug-2024844-Feb-2024948-Mar-20248529-May-2024157-Oct-202427
7NAME460017-Jan-202411-Dec-20249527-Nov-20247318-Dec-20241318-Mar-20245220-Apr-2024412-Apr-20248730-Apr-20248722-Feb-202438
8NAME17006-Jan-202425-Jul-20244212-Jul-20241312-Dec-20243410-Jul-2024751-Apr-202422
9NAME58005-Jan-202424-Dec-2024342-Aug-20249021-Oct-2024529-Feb-20245125-Sep-2024827-Dec-20242226-Feb-202454
10NAME190017-Jan-202428-May-20249719-Apr-2024688-Aug-20247329-Dec-20246319-Oct-20246211-Dec-20242619-Oct-2024738-Sep-202455
11NAME1100015-Jan-20247-Aug-2024302-Dec-20243313-Sep-20248225-Jun-2024100
12NAME6110014-Jan-202428-Jun-2024676-May-20249217-Feb-20242521-Nov-20244124-Sep-20248113-Apr-20246221-Oct-2024874-Jun-202410
13NAME312007-Jan-202416-Dec-20243329-Dec-2024443-May-20244129-Dec-20246726-Apr-2024845-Jun-20241831-Aug-202495
14NAME213003-Jan-20241-Jul-20247814-Apr-20246612-Aug-20242217-Apr-20246614-Nov-20242511-Dec-202453
15NAME414001-Jan-202417-Apr-2024199-Oct-20243927-Sep-20246213-Oct-20248126-Nov-20247224-Aug-2024596-May-202413
16NAME3150029-Jan-20241-Apr-2024379-May-2024614-Jul-20241005-Dec-20242510-Oct-20243914-Feb-20243021-Sep-202479
17
18
19
20
21RESULT
22NAMENAME1
23
24NAMEAMOUNTDATEDATEAMOUNT
25NAME130010-Jan-202411-Sep-202437
2620-Dec-202410
2723-Sep-202488
2819-Apr-202478
29NAME170006-Jan-202425-Jul-202442
3012-Jul-202413
3112-Dec-202434
3210-Jul-202475
331-Apr-202422
34NAME190017-Jan-202428-May-202497
3519-Apr-202468
368-Aug-202473
3729-Dec-202463
3819-Oct-202462
3911-Dec-202426
4019-Oct-202473
418-Sep-202455
42NAME1100015-Jan-20247-Aug-202430
432-Dec-202433
4413-Sep-202482
4525-Jun-2024100
46
Sheet1
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
What is the difference between DATE in col C and the others? A kind of " join date" ?
 
Upvote 0
How about
Excel Formula: 
=LET(f,FILTER(A4:T17,A4:A17=G22),w,WRAPROWS(TOCOL(DROP(f,,4),1),2),HSTACK(DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(x,y,VSTACK(x,EXPAND(CHOOSEROWS(TAKE(f,,3),y),SUM(--(DROP(CHOOSEROWS(f,y),,4)<>""))/2,,"")))),1),w))
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=LET(f,FILTER(A4:T17,A4:A17=G22),w,WRAPROWS(TOCOL(DROP(f,,4),1),2),HSTACK(DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(x,y,VSTACK(x,EXPAND(CHOOSEROWS(TAKE(f,,3),y),SUM(--(DROP(CHOOSEROWS(f,y),,4)<>""))/2,,"")))),1),w))
Click to expand...
many thanks.. it works in the sample file..but i could not make it work in my actual file. i figured that it might be because i have other values in column E to I..
my apology, i never thought that it will have a bearing..would it be possible to tweak the formula to ignore all the values in column E to I?. thank you


1712583402075.png
 
Upvote 0
Try
Excel Formula: 
=LET(f,FILTER(B4:Y17,B4:B17=L22),w,WRAPROWS(TOCOL(DROP(f,,8),1),2),HSTACK(DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(x,y,VSTACK(x,EXPAND(CHOOSEROWS(TAKE(f,,3),y),SUM(--(DROP(CHOOSEROWS(f,y),,8)<>""))/2,,"")))),1),w))
 
Upvote 0
Fluff said:
Try
Excel Formula: 
=LET(f,FILTER(B4:Y17,B4:B17=L22),w,WRAPROWS(TOCOL(DROP(f,,8),1),2),HSTACK(DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(x,y,VSTACK(x,EXPAND(CHOOSEROWS(TAKE(f,,3),y),SUM(--(DROP(CHOOSEROWS(f,y),,8)<>""))/2,,"")))),1),w))
Click to expand...
thanks man, really appreciate it..i finally make it work.. i tried play with the sample data..i noticed that the formula returns #value! if there is an empty range column J to Y. for example name1 and name10, i added an empty range.. both of them return #value.. is there a way to fix it?. if the range is empty it will return empty (in yellow).. thank you

1712642761634.png
 
Upvote 0
An answer to my question would be appreciated...
 
Upvote 0
Try
Excel Formula: 
LET(a,FILTER(A5:T17,A5:A17="NAME1"),aa,FILTER(E5:T17,A5:A17="NAME1"),b,BYROW(a,LAMBDA(r,LET(cnt,SUMPRODUCT((r<>"")*(A4:T4="AMOUNT"))-2,INDEX(r,1)&","&INDEX(r,2)&","&INDEX(r,3)&REPT(",",cnt*3)))),c,TEXTSPLIT(TEXTJOIN(",",FALSE,b),",",,FALSE,1),d,WRAPROWS(c,3,""),e,(HSTACK(INDEX(d,,1),INDEX(d,,2)+0,INDEX(d,,3)+0)),f,IFERROR(e,""),g,WRAPROWS(TOCOL(aa,1,),2,""),HSTACK(f,g))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Select current month in combobox on userform for English language
2 3
Replies
29
Views
319
Ali M
A
S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
79
AhoyNC
A
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
672
Joe4
Joe4
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
35
HighAndWilder
H
D
  • Locked
  • Question Question
Could you please write a code for the following scenario?
Replies
1
Views
183
Fluff
Fluff

Forum statistics

Threads
1,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top