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 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.
Share Improve this question edited Mar 31 at 22:36 Ranjan Kumar Singh asked Mar 31 at 13:09 Ranjan Kumar SinghRanjan Kumar Singh 5710 bronze badges 2 |1 Answer
Reset to default 2You may try:
=map(sort(unique(tocol(AZ4:AZ,1)),1,),lambda(Σ,hstack(Σ,torow(filter(BA:BG,AZ:AZ=Σ)))))
google-sheetstag:Do NOT share spreadsheets/images as the only source of data. Use markdown TEXT tables instead.Please provide markdown tables containing your sampleinputdata and desiredoutput. You may create one with the help of this link. – PatrickdC Commented Mar 31 at 13:27