JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Extract Date sorted list from multiple columns stored in a table
Afternoon All,
I thought it would be a good idea to have my data inputted horizontally instead of vertically as I got bored of scrolling to the bottom of a table to add more data and it was easy to see the transactions for each company's shares. Now I have the issue that I cannot easily see what the last share transactions were (Make one job easy just to make a subsequent job ten times harder - seems to be a trend in the way I work).
If anybody can help I'm looking to create a list similar to C4 but also pulls in the data from not only from column I4 (Date1)but L4, O4, S4 (Date2,3 and 4 respectively) and potentially other columns as the data table increases over time.
The table will always consist of the headers (Name, Date, Shares and ShareCost) with the suffix of 1,2,3 etc.
The new table would look a bit like this
Name Date Shares Cost HLMA.LSE 23/01/2023 45 2141.64 SPX.LSE 23/01/2023 8 11543.6 BME.LSE 14/02/2023 207 476.69 SGE.LSE 27/02/2023 130 755.71 HLMA.LSE 07/03/2023 -45 2141.64 SPX.LSE 07/03/2023 -8 11543.6
Any help would be gratefully received
Regards
Ian
Book1 A B C D E F G H I J K L M N O P Q R S T U 1 2 3 Name Date Shares Cost Name Date1 Shares1 ShareCost1 Date2 Shares2 ShareCost2 Date3 Shares3 ShareCost3 Date4 Shares4 ShareCost4 4 HLMA.LSE 23/01/2023 45 2141.64 BA..LSE 12/05/2023 105 988.4 22/05/2023 -105 988.4 5 SPX.LSE 23/01/2023 8 11543.6 BATS.LSE 13/03/2023 45 3040.5 16/05/2023 35 2738.5277 6 BME.LSE 14/02/2023 207 476.69 BME.LSE 14/02/2023 207 476.69 12/06/2023 28 563.1786 7 SGE.LSE 27/02/2023 130 755.71 CRDA.LSE 12/05/2023 13 6835 22/05/2023 -13 6835 8 BATS.LSE 13/03/2023 45 3040.5 CGS.LSE 15/05/2023 261 376.5 9 CSN.LSE 13/03/2023 340 284 CSN.LSE 13/03/2023 340 284 16/05/2023 342 286.65 13/06/2023 18 283.3889 10 GLEN.LSE 13/03/2023 378 456.6 DCC.LSE 12/05/2023 20 4732.5 11 GSK.LSE 13/03/2023 20 1387 DGE.LSE 12/05/2023 4 3528.25 12 KETL.LSE 13/03/2023 956 92.8 GLEN.LSE 13/03/2023 378 456.6 16/05/2023 225 437.237 05/06/2023 15 435.8625 13 LGEN.LSE 13/03/2023 378 241.3 GSK.LSE 13/03/2023 20 1387 12/05/2023 44 1481.9 14 MNG.LSE 13/03/2023 471 207.5 HLMA.LSE 23/01/2023 45 2141.64 07/03/2023 -45 2141.64 15 MONY.LSE 13/03/2023 1300 232.4 KETL.LSE 13/03/2023 956 92.8 16 NG..LSE 13/03/2023 91 1064.25 LGEN.LSE 13/03/2023 378 241.3 16/05/2023 423 231.9098 13/06/2023 22 238.1818 17 PHNX.LSE 13/03/2023 153 598.6 LMP.LSE 15/05/2023 521 188.472 18 PHP.LSE 13/03/2023 1349 100.6 MGNS.LSE 22/05/2023 52 1857.2 19 PSN.LSE 13/03/2023 66 1217.25 MNG.LSE 13/03/2023 471 207.5 16/05/2023 492 199.696 20 RIO.LSE 13/03/2023 31 5462 MONY.LSE 13/03/2023 1300 232.4 13/06/2023 42 262.7143 21 THRL.LSE 13/03/2023 1204 70.95 NG..LSE 13/03/2023 91 1064.25 22 VOD.LSE 13/03/2023 2042 96.615 PHNX.LSE 13/03/2023 153 598.6 16/05/2023 170 575.37 13/06/2023 6 553.5 23 VTY.LSE 13/03/2023 129 762.25 PHP.LSE 13/03/2023 1349 100.6 13/06/2023 22 100.818 24 BA..LSE 12/05/2023 105 988.4 PSN.LSE 13/03/2023 66 1217.25 25 CRDA.LSE 12/05/2023 13 6835 RIO.LSE 13/03/2023 31 5462 16/05/2023 19 4966.5 26 DCC.LSE 12/05/2023 20 4732.5 SGE.LSE 27/02/2023 130 755.71 07/03/2023 -130 755.71 27 DGE.LSE 12/05/2023 4 3528.25 SPX.LSE 23/01/2023 8 11543.6 07/03/2023 -8 11543.6 28 ULVR.LSE 12/05/2023 68 4340 SRE.LSE 15/05/2023 1262 78.246 29 UU..LSE 12/05/2023 93 1077 THRL.LSE 13/03/2023 1204 70.95 16/05/2023 1190 82.573 13/06/2023 22 75.1364 30 CGS.LSE 15/05/2023 261 376.5 TND.LSE 22/05/2023 359 247.2 31 LMP.LSE 15/05/2023 521 188.472 ULVR.LSE 12/05/2023 68 4340 32 SRE.LSE 15/05/2023 1262 78.246 UU..LSE 12/05/2023 93 1077 33 MGNS.LSE 22/05/2023 52 1857.2 VOD.LSE 13/03/2023 2042 96.615 16/05/2023 1137 86.457 16/05/2023 512 85 34 TND.LSE 22/05/2023 359 247.2 VTY.LSE 13/03/2023 129 762.25 13/06/2023 5 753.8 35 36
Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Not sure if the below will help as I don't know how many columns there would be:
Book1 A B C D E F G H I J K L M N O 1 Name Date1 Shares1 ShareCost1 Date2 Shares2 ShareCost2 Date3 Shares3 ShareCost3 Name Date Shares Cost 2 BA..LSE 12/05/2023 105 988.4 22/05/2023 -105 988.4 HLMA.LSE 23/01/2023 45 2141.64 3 BATS.LSE 13/03/2023 45 3040.5 16/05/2023 35 2738.5277 SPX.LSE 23/01/2023 8 11543.6 4 BME.LSE 14/02/2023 207 476.69 12/06/2023 28 563.1786 BME.LSE 14/02/2023 207 476.69 5 CRDA.LSE 12/05/2023 13 6835 22/05/2023 -13 6835 SGE.LSE 27/02/2023 130 755.71 6 CGS.LSE 15/05/2023 261 376.5 HLMA.LSE 07/03/2023 -45 2141.64 7 CSN.LSE 13/03/2023 340 284 16/05/2023 342 286.65 13/06/2023 18 283.3889 SGE.LSE 07/03/2023 -130 755.71 8 DCC.LSE 12/05/2023 20 4732.5 SPX.LSE 07/03/2023 -8 11543.6 9 DGE.LSE 12/05/2023 4 3528.25 BATS.LSE 13/03/2023 45 3040.5 10 GLEN.LSE 13/03/2023 378 456.6 16/05/2023 225 437.237 05/06/2023 15 435.8625 CSN.LSE 13/03/2023 340 284 11 GSK.LSE 13/03/2023 20 1387 12/05/2023 44 1481.9 GLEN.LSE 13/03/2023 378 456.6 12 HLMA.LSE 23/01/2023 45 2141.64 07/03/2023 -45 2141.64 GSK.LSE 13/03/2023 20 1387 13 KETL.LSE 13/03/2023 956 92.8 KETL.LSE 13/03/2023 956 92.8 14 LGEN.LSE 13/03/2023 378 241.3 16/05/2023 423 231.9098 13/06/2023 22 238.1818 LGEN.LSE 13/03/2023 378 241.3 15 LMP.LSE 15/05/2023 521 188.472 MNG.LSE 13/03/2023 471 207.5 16 MGNS.LSE 22/05/2023 52 1857.2 MONY.LSE 13/03/2023 1300 232.4 17 MNG.LSE 13/03/2023 471 207.5 16/05/2023 492 199.696 NG..LSE 13/03/2023 91 1064.25 18 MONY.LSE 13/03/2023 1300 232.4 13/06/2023 42 262.7143 PHNX.LSE 13/03/2023 153 598.6 19 NG..LSE 13/03/2023 91 1064.25 PHP.LSE 13/03/2023 1349 100.6 20 PHNX.LSE 13/03/2023 153 598.6 16/05/2023 170 575.37 13/06/2023 6 553.5 PSN.LSE 13/03/2023 66 1217.25 21 PHP.LSE 13/03/2023 1349 100.6 13/06/2023 22 100.818 RIO.LSE 13/03/2023 31 5462 22 PSN.LSE 13/03/2023 66 1217.25 THRL.LSE 13/03/2023 1204 70.95 23 RIO.LSE 13/03/2023 31 5462 16/05/2023 19 4966.5 VOD.LSE 13/03/2023 2042 96.615 24 SGE.LSE 27/02/2023 130 755.71 07/03/2023 -130 755.71 VTY.LSE 13/03/2023 129 762.25 25 SPX.LSE 23/01/2023 8 11543.6 07/03/2023 -8 11543.6 BA..LSE 12/05/2023 105 988.4 26 SRE.LSE 15/05/2023 1262 78.246 CRDA.LSE 12/05/2023 13 6835 27 THRL.LSE 13/03/2023 1204 70.95 16/05/2023 1190 82.573 13/06/2023 22 75.1364 DCC.LSE 12/05/2023 20 4732.5 28 TND.LSE 22/05/2023 359 247.2 DGE.LSE 12/05/2023 4 3528.25 29 ULVR.LSE 12/05/2023 68 4340 ULVR.LSE 12/05/2023 68 4340 30 UU..LSE 12/05/2023 93 1077 UU..LSE 12/05/2023 93 1077 31 VOD.LSE 13/03/2023 2042 96.615 16/05/2023 1137 86.457 16/05/2023 512 85 GSK.LSE 12/05/2023 44 1481.9 32 VTY.LSE 13/03/2023 129 762.25 13/06/2023 5 753.8 CGS.LSE 15/05/2023 261 376.5 33 LMP.LSE 15/05/2023 521 188.472 34 SRE.LSE 15/05/2023 1262 78.246 35 BATS.LSE 16/05/2023 35 2738.528 36 CSN.LSE 16/05/2023 342 286.65 37 GLEN.LSE 16/05/2023 225 437.237 38 LGEN.LSE 16/05/2023 423 231.9098 39 MNG.LSE 16/05/2023 492 199.696 40 PHNX.LSE 16/05/2023 170 575.37 41 RIO.LSE 16/05/2023 19 4966.5 42 THRL.LSE 16/05/2023 1190 82.573 43 VOD.LSE 16/05/2023 1137 86.457 44 VOD.LSE 16/05/2023 512 85 45 MGNS.LSE 22/05/2023 52 1857.2 46 TND.LSE 22/05/2023 359 247.2 47 BA..LSE 22/05/2023 -105 988.4 48 CRDA.LSE 22/05/2023 -13 6835 49 GLEN.LSE 05/06/2023 15 435.8625 50 BME.LSE 12/06/2023 28 563.1786 51 MONY.LSE 13/06/2023 42 262.7143 52 PHP.LSE 13/06/2023 22 100.818 53 VTY.LSE 13/06/2023 5 753.8 54 CSN.LSE 13/06/2023 18 283.3889 55 LGEN.LSE 13/06/2023 22 238.1818 56 PHNX.LSE 13/06/2023 6 553.5 57 THRL.LSE 13/06/2023 22 75.1364
@Georgiboy thanks for that it works, but being lazy I was looking for a solution that would automatically grow if the input table grows without me having to change the formula. Your solution does work perfectly and I will use it for the time being.
Thanks for your help
Ian
Another option
Fluff.xlsm A B C D E F G H I J K L M N O 1 Name Date1 Shares1 ShareCost1 Date2 Shares2 ShareCost2 Date3 Shares3 ShareCost3 Name Date Shares Cost 2 BA..LSE 12/05/2023 105 988.40 22/05/2023 -105 988.40 HLMA.LSE 23/01/2023 45 2141.64 3 BATS.LSE 13/03/2023 45 3040.50 16/05/2023 35 2738.53 SPX.LSE 23/01/2023 8 11543.60 4 BME.LSE 14/02/2023 207 476.69 12/06/2023 28 563.18 BME.LSE 14/02/2023 207 476.69 5 CRDA.LSE 12/05/2023 13 6835.00 22/05/2023 -13 6835.00 SGE.LSE 27/02/2023 130 755.71 6 CGS.LSE 15/05/2023 261 376.50 HLMA.LSE 07/03/2023 -45 2141.64 7 CSN.LSE 13/03/2023 340 284.00 16/05/2023 342 286.65 13/06/2023 18 283.39 SGE.LSE 07/03/2023 -130 755.71 8 DCC.LSE 12/05/2023 20 4732.50 SPX.LSE 07/03/2023 -8 11543.60 9 DGE.LSE 12/05/2023 4 3528.25 BATS.LSE 13/03/2023 45 3040.50 10 GLEN.LSE 13/03/2023 378 456.60 16/05/2023 225 437.24 05/06/2023 15 435.86 CSN.LSE 13/03/2023 340 284.00 11 GSK.LSE 13/03/2023 20 1387.00 12/05/2023 44 1481.90 GLEN.LSE 13/03/2023 378 456.60 12 HLMA.LSE 23/01/2023 45 2141.64 07/03/2023 -45 2141.64 GSK.LSE 13/03/2023 20 1387.00 13 KETL.LSE 13/03/2023 956 92.80 KETL.LSE 13/03/2023 956 92.80 14 LGEN.LSE 13/03/2023 378 241.30 16/05/2023 423 231.91 13/06/2023 22 238.18 LGEN.LSE 13/03/2023 378 241.30 15 LMP.LSE 15/05/2023 521 188.47 MNG.LSE 13/03/2023 471 207.50 16 MGNS.LSE 22/05/2023 52 1857.20 MONY.LSE 13/03/2023 1300 232.40 17 MNG.LSE 13/03/2023 471 207.50 16/05/2023 492 199.70 NG..LSE 13/03/2023 91 1064.25 18 MONY.LSE 13/03/2023 1300 232.40 13/06/2023 42 262.71 PHNX.LSE 13/03/2023 153 598.60 19 NG..LSE 13/03/2023 91 1064.25 PHP.LSE 13/03/2023 1349 100.60 20 PHNX.LSE 13/03/2023 153 598.60 16/05/2023 170 575.37 13/06/2023 6 553.50 PSN.LSE 13/03/2023 66 1217.25 21 PHP.LSE 13/03/2023 1349 100.60 13/06/2023 22 100.82 RIO.LSE 13/03/2023 31 5462.00 22 PSN.LSE 13/03/2023 66 1217.25 THRL.LSE 13/03/2023 1204 70.95 23 RIO.LSE 13/03/2023 31 5462.00 16/05/2023 19 4966.50 VOD.LSE 13/03/2023 2042 96.62 24 SGE.LSE 27/02/2023 130 755.71 07/03/2023 -130 755.71 VTY.LSE 13/03/2023 129 762.25 25 SPX.LSE 23/01/2023 8 11543.60 07/03/2023 -8 11543.60 BA..LSE 12/05/2023 105 988.40 26 SRE.LSE 15/05/2023 1262 78.25 CRDA.LSE 12/05/2023 13 6835.00 27 THRL.LSE 13/03/2023 1204 70.95 16/05/2023 1190 82.57 13/06/2023 22 75.14 DCC.LSE 12/05/2023 20 4732.50 28 TND.LSE 22/05/2023 359 247.20 DGE.LSE 12/05/2023 4 3528.25 29 ULVR.LSE 12/05/2023 68 4340.00 GSK.LSE 12/05/2023 44 1481.90 30 UU..LSE 12/05/2023 93 1077.00 ULVR.LSE 12/05/2023 68 4340.00 31 VOD.LSE 13/03/2023 2042 96.62 16/05/2023 1137 86.46 16/05/2023 512 85.00 UU..LSE 12/05/2023 93 1077.00 32 VTY.LSE 13/03/2023 129 762.25 13/06/2023 5 753.80 CGS.LSE 15/05/2023 261 376.50 33 LMP.LSE 15/05/2023 521 188.47 34 SRE.LSE 15/05/2023 1262 78.25 35 BATS.LSE 16/05/2023 35 2738.53 36 CSN.LSE 16/05/2023 342 286.65 37 GLEN.LSE 16/05/2023 225 437.24 38 LGEN.LSE 16/05/2023 423 231.91 39 MNG.LSE 16/05/2023 492 199.70 40 PHNX.LSE 16/05/2023 170 575.37 41 RIO.LSE 16/05/2023 19 4966.50 42 THRL.LSE 16/05/2023 1190 82.57 43 VOD.LSE 16/05/2023 1137 86.46 44 VOD.LSE 16/05/2023 512 85.00 45 BA..LSE 22/05/2023 -105 988.40 46 CRDA.LSE 22/05/2023 -13 6835.00 47 MGNS.LSE 22/05/2023 52 1857.20 48 TND.LSE 22/05/2023 359 247.20 49 GLEN.LSE 05/06/2023 15 435.86 50 BME.LSE 12/06/2023 28 563.18 51 CSN.LSE 13/06/2023 18 283.39 52 LGEN.LSE 13/06/2023 22 238.18 53 MONY.LSE 13/06/2023 42 262.71 54 PHNX.LSE 13/06/2023 6 553.50 55 PHP.LSE 13/06/2023 22 100.82 56 THRL.LSE 13/06/2023 22 75.14 57 VTY.LSE 13/06/2023 5 753.80
Once again thanks
@Fluff perfect solution and suits my lazy lifestyle and automatically expands as I add columns to the original table
Ian
Glad we could help & thanks for the feedback.
