Need to transpose data from column to row but in different way

E

excel_beta_345User

New Member
Joined
Jun 17, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Before I begin, i would like to really apologize to everyone that I am not able to use XL2BB to ease your help. My office365 does not allow me to add that add-in, I failed multiple times.

As you see in the attached image, Column B contains names and those might get repetitive for the every fruit they have ordered as per Column C.
For every fruit in Column C, there are some characteristics associated which are displayed in Column D & E.

I am trying to create unique list out of Column B and transpose everything from Column C,D,E in row. One row with all information for one name, irrespective of how columns are added but row should be single for that name. I tried it doing hard way as shown in the image but given I have 5000 names with 2000 unique names and have 8000 fruits records against them with some columns of characteristics associated with those 8000 fruits, that hard way has became really chaotic and time consuming for me. How can I easily transpose those details into single row per name. Can someone please guide me there.
 

Attachments

  • excel.png
    excel.png
    99.4 KB · Views: 7

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
How about
Excel Formula: 
=LET(g,DROP(GROUPBY(B2:B9000,C2:C9000&"|"&D2:D9000&"|"&E2:E9000,HSTACK(LAMBDA(v,"|"&TEXTJOIN("|",,v)),COUNTA),0,0,,B2:B9000<>""),1),c,TAKE(g,,-1)*3,m,MAX(c),HSTACK(TAKE(g,,1),TEXTBEFORE(TEXTAFTER(INDEX(g,,2)&REPT("|",m-c+1),"|",SEQUENCE(,m)),"|")))
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=LET(g,DROP(GROUPBY(B2:B9000,C2:C9000&"|"&D2:D9000&"|"&E2:E9000,HSTACK(LAMBDA(v,TEXTJOIN("|",,v)),COUNTA),0,0,,B2:B9000<>""),1),c,TAKE(g,,-1)*3,m,MAX(c),HSTACK(TAKE(g,,1),TEXTBEFORE(TEXTAFTER(INDEX(g,,2)&REPT("|",m-c+1),"|",SEQUENCE(,m-1)),"|")))
Click to expand...
I am supposed to add this in next column right? it gives me error like "#NAME?"... While evaluating formula, GROUPBY converts into #NAME
 
Upvote 0
You obviously don't have the groupby function yet. Are you on the semi-annual channel?
 
Upvote 0
Two options, the 1st in H2 could take a while with 2000 distinct values in col B
Fluff.xlsm
BCDEFGHIJKLMNOPQRST
1CountyDistrictWardIntroduced
2West YorkshireKirkleesLindley01/01/1980West YorkshireKirkleesLindley29221KirkleesHolme Valley North37043BradfordTong35582
3South YorkshireBarnsleyPenistone East01/04/1985South YorkshireBarnsleyPenistone East31138
4LancashireRossendaleLongholme01/01/1980LancashireRossendaleLongholme29221
5Greater ManchesterTamesideHyde Werneth01/08/1993Greater ManchesterTamesideHyde Werneth34182TamesideMossley29221
6West YorkshireKirkleesHolme Valley North01/06/2001West MidlandsSandwellOld Warley29221
7West MidlandsSandwellOld Warley01/01/1980StaffordshireStoke-on-TrentGoldenhill and Sandyford29221Stoke-on-TrentMeir South29221Newcastle-under-LymeLoggerheads and Whitmore29221Staffordshire MoorlandsLeek East29221
8StaffordshireStoke-on-TrentGoldenhill and Sandyford01/01/1980DevonWest DevonBridestowe29221
9StaffordshireStoke-on-TrentMeir South01/01/1980HerefordshireHerefordshire, County ofGolden Valley South29221
10Greater ManchesterTamesideMossley01/01/1980
11StaffordshireNewcastle-under-LymeLoggerheads and Whitmore01/01/1980West YorkshireKirkleesLindley29221KirkleesHolme Valley North37043BradfordTong35582
12DevonWest DevonBridestowe01/01/1980South YorkshireBarnsleyPenistone East31138
13West YorkshireBradfordTong01/06/1997LancashireRossendaleLongholme29221
14StaffordshireStaffordshire MoorlandsLeek East01/01/1980Greater ManchesterTamesideHyde Werneth34182TamesideMossley29221
15HerefordshireHerefordshire, County ofGolden Valley South01/01/1980West MidlandsSandwellOld Warley29221
16StaffordshireStoke-on-TrentGoldenhill and Sandyford29221Stoke-on-TrentMeir South29221Newcastle-under-LymeLoggerheads and Whitmore29221Staffordshire MoorlandsLeek East29221
17DevonWest DevonBridestowe29221
18HerefordshireHerefordshire, County ofGolden Valley South29221
19
Data
Cell Formulas
RangeFormula
H2:T9H2=LET(u,UNIQUE(FILTER(B2:B9000,B2:B9000<>"")),m,MAX(COUNTIFS(B:B,u))*3,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(C2:E9000,B2:B9000=y)),,m,""))))),1))
H11:H18H11=UNIQUE(FILTER(B2:B9000,B2:B9000<>""))
I11:Q11,I17:K18,I16:T16,I15:K15,I14:N14,I12:K13I11=TOROW(FILTER(C2:E9000,B2:B9000=H11))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Data!$B$1:$E$8663H2
 
Upvote 0
Fluff said:
Two options, the 1st in H2 could take a while with 2000 distinct values in col B
Fluff.xlsm
BCDEFGHIJKLMNOPQRST
1CountyDistrictWardIntroduced
2West YorkshireKirkleesLindley01/01/1980West YorkshireKirkleesLindley29221KirkleesHolme Valley North37043BradfordTong35582
3South YorkshireBarnsleyPenistone East01/04/1985South YorkshireBarnsleyPenistone East31138
4LancashireRossendaleLongholme01/01/1980LancashireRossendaleLongholme29221
5Greater ManchesterTamesideHyde Werneth01/08/1993Greater ManchesterTamesideHyde Werneth34182TamesideMossley29221
6West YorkshireKirkleesHolme Valley North01/06/2001West MidlandsSandwellOld Warley29221
7West MidlandsSandwellOld Warley01/01/1980StaffordshireStoke-on-TrentGoldenhill and Sandyford29221Stoke-on-TrentMeir South29221Newcastle-under-LymeLoggerheads and Whitmore29221Staffordshire MoorlandsLeek East29221
8StaffordshireStoke-on-TrentGoldenhill and Sandyford01/01/1980DevonWest DevonBridestowe29221
9StaffordshireStoke-on-TrentMeir South01/01/1980HerefordshireHerefordshire, County ofGolden Valley South29221
10Greater ManchesterTamesideMossley01/01/1980
11StaffordshireNewcastle-under-LymeLoggerheads and Whitmore01/01/1980West YorkshireKirkleesLindley29221KirkleesHolme Valley North37043BradfordTong35582
12DevonWest DevonBridestowe01/01/1980South YorkshireBarnsleyPenistone East31138
13West YorkshireBradfordTong01/06/1997LancashireRossendaleLongholme29221
14StaffordshireStaffordshire MoorlandsLeek East01/01/1980Greater ManchesterTamesideHyde Werneth34182TamesideMossley29221
15HerefordshireHerefordshire, County ofGolden Valley South01/01/1980West MidlandsSandwellOld Warley29221
16StaffordshireStoke-on-TrentGoldenhill and Sandyford29221Stoke-on-TrentMeir South29221Newcastle-under-LymeLoggerheads and Whitmore29221Staffordshire MoorlandsLeek East29221
17DevonWest DevonBridestowe29221
18HerefordshireHerefordshire, County ofGolden Valley South29221
19
Data
Cell Formulas
RangeFormula
H2:T9H2=LET(u,UNIQUE(FILTER(B2:B9000,B2:B9000<>"")),m,MAX(COUNTIFS(B:B,u))*3,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(C2:E9000,B2:B9000=y)),,m,""))))),1))
H11:H18H11=UNIQUE(FILTER(B2:B9000,B2:B9000<>""))
I11:Q11,I17:K18,I16:T16,I15:K15,I14:N14,I12:K13I11=TOROW(FILTER(C2:E9000,B2:B9000=H11))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Data!$B$1:$E$8663H2
Click to expand...
This worked, thank you so much for the help and time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Proiblem Transposing Named Range Data To A Column
Replies
7
Views
82
Fluff
Fluff
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
472
sijpie
sijpie
R
  • Question Question
VBA Inserting rows based on Column criteria
Replies
4
Views
229
myall_blues
myall_blues
S
  • Question Question
What's the best/most efficient way to set Variables for Columns
Replies
9
Views
359
dmt32
D
K
  • Question Question
Unique & Filter from multiple tables into a single list
Replies
9
Views
403
K1600
K

Forum statistics

Threads
1,225,739
Messages
6,186,738
Members
453,369
Latest member
juliewar

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