google-sheetsgoogle-sheets-formula

How to combine rows into columns based on finding unique values and convert it into rows?


I have an array(AZ4:BG):

ZYDUSLIFE 4/24/2025 900 888.75 142097.62 142097.62 142097.62 799875
ZYDUSLIFE 5/29/2025 900 893.85 143059.28 143059.28 143059.28 804465
ZYDUSLIFE 6/26/2025 900 900.7 143914.05 143914.05 143914.05 810630
ZOMATO 4/24/2025 2000 202.15 108330.5 108330.5 108330.5 404300
ZOMATO 5/29/2025 2000 203.07 109054.9 109054.9 109054.9 406140
ZOMATO 6/26/2025 2000 204.5 109675 109675 109675 409000
YESBANK 4/24/2025 26000 16.92 106917.2 106917.2 106917.2 439920
YESBANK 5/29/2025 26000 17.04 107806.4 107806.4 107806.4 443040
YESBANK 6/26/2025 26000 17.14 108417.4 108417.4 108417.4 445640
WIPRO 4/24/2025 3000 261.7 146218.5 146218.5 146218.5 785100
WIPRO 5/29/2025 3000 261.65 147053.25 147053.25 147053.25 784950
WIPRO 6/26/2025 3000 262.25 147776.25 147776.25 147776.25 786750
VOLTAS 4/24/2025 300 1435.65 99257.33 99257.33 99257.33 430695
VOLTAS 5/29/2025 300 1436.8 99860.4 99860.4 99860.4 431040
VOLTAS 6/26/2025 300 1439.5 100365.75 100365.75 100365.75 431850
VEDL 4/24/2025 1150 465.25 118155.31 118155.31 118155.31 535037.5
VEDL 5/29/2025 1150 467.95 118965.49 118965.49 118965.49 538142.5
VEDL 6/26/2025 1150 471.3 119663.83 119663.83 119663.83 541995
VBL 4/24/2025 875 542.2 109162.88 109162.88 109162.88 474425
VBL 5/29/2025 875 545.35 109906.34 109906.34 109906.34 477181.25
VBL 6/26/2025 875 549.8 110567.63 110567.63 110567.63 481075
UPL 4/24/2025 1355 637.65 166404.55 166404.55 166404.55 864015.75
UPL 5/29/2025 1355 641.45 167546.77 167546.77 167546.77 869164.75
UPL 6/26/2025 1355 644.75 168475.62 168475.62 168475.62 873636.25

I have found unique values using formaula:

=sort(unique(AZ4:AZ), 1, FALSE)

Based on unique values, I want to match col1 and combine the remaining columns into rows.

My expected results:

ZYDUSLIFE 4/24/2025 900 888.75 142097.62 142097.62 142097.62 799875 5/29/2025 900 893.85 143059.28 143059.28 143059.28 804465 6/26/2025 900 900.7 143914.05 143914.05 143914.05 810630
ZOMATO 4/24/2025 2000 202.15 108330.5 108330.5 108330.5 404300 5/29/2025 2000 203.07 109054.9 109054.9 109054.9 406140 6/26/2025 2000 204.5 109675 109675 109675 409000
YESBANK 4/24/2025 26000 16.92 106917.2 106917.2 106917.2 439920 5/29/2025 26000 17.04 107806.4 107806.4 107806.4 443040 6/26/2025 26000 17.14 108417.4 108417.4 108417.4 445640
WIPRO 4/24/2025 3000 261.7 146218.5 146218.5 146218.5 785100 5/29/2025 3000 261.65 147053.25 147053.25 147053.25 784950 6/26/2025 3000 262.25 147776.25 147776.25 147776.25 786750
VOLTAS 4/24/2025 300 1435.65 99257.33 99257.33 99257.33 430695 5/29/2025 300 1436.8 99860.4 99860.4 99860.4 431040 6/26/2025 300 1439.5 100365.75 100365.75 100365.75 431850
VEDL 4/24/2025 1150 465.25 118155.31 118155.31 118155.31 535037.5 5/29/2025 1150 467.95 118965.49 118965.49 118965.49 538142.5 6/26/2025 1150 471.3 119663.83 119663.83 119663.83 541995
VBL 4/24/2025 875 542.2 109162.88 109162.88 109162.88 474425 5/29/2025 875 545.35 109906.34 109906.34 109906.34 477181.25 6/26/2025 875 549.8 110567.63 110567.63 110567.63 481075
UPL 4/24/2025 1355 637.65 166404.55 166404.55 166404.55 864015.75 5/29/2025 1355 641.45 167546.77 167546.77 167546.77 869164.75 6/26/2025 1355 644.75 168475.62 168475.62 168475.62 873636.25

I want to do it dynamically using any formula, kindly help me.


Solution

  • You may try:

    =map(sort(unique(tocol(AZ4:AZ,1)),1,),lambda(Σ,hstack(Σ,torow(filter(BA:BG,AZ:AZ=Σ)))))