Collect data from multiple sources to form a table of data in a specific order

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hi there Excel Guru's,

Do excuse the title of this one...

Below is a table of data.
What I need to do is gather the data from respective cells and collate this data into a simple format to ascertain the best values

E2.PNG


For example
Gather all the setting no.s (ranked biggest to smallest)
The pair is related to the setting number
Account balance next in relation to the setting number
The total trades - again in relation to the setting number
Etc...

Drafted example (Possible layout)

account balance£88126£64287£57001£51085
setting no. (in order biggest > smallest)76572821
total trades386010196
pairAUDJPYAUDCADAUDCHFAUDNZD
I may require more data gathering in this row


There are 28 pairs (columns) of data.
The idea is to understand the setting no. and which setting (group of numbers) produces the best return.

I hope this makes sense.

I have enclosed a copy, but its values only - so no formulas
The reason why some of the columns show #DIV/0! is the data produced at setting one = zero. I.E The report displayed no values for this setting.
I know how to set the cell to blank or 0

If you require further info, please just message me.

My thanks and appreciation
Copperwasher
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,060
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Cannot manipulate data from a picture Please reload the data to this site using XL2BB. Also, looking at your picture, you have what appears to be lots of merged cells. Look at this link on why this is not a good practice.


 

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Cannot manipulate data from a picture Please reload the data to this site using XL2BB. Also, looking at your picture, you have what appears to be lots of merged cells. Look at this link on why this is not a good practice.




MrExcel 1000. CCIColour Period1 Batch1.1.1.100 TradesAll OptiResultsReport V8.0.xlsx
A
25
ReportSingle Performance


Here you go... first time using the capture add-in...

Hopefully its worked
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,060
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You need to highlight the range you wish to upload first and then use XL2BB. You have uploaded an empty range.
 

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
You need to highlight the range you wish to upload first and then use XL2BB. You have uploaded an empty range.

MrExcel 1000. CCIColour Period1 Batch1.1.1.100 TradesAll OptiResultsReport V8.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
128AUDCADAUDCHFAUDJPYAUDNZDAUDUSDCADCHFCADJPYCHFJPYEURAUDEURCADEURCHFEURGBPEURJPYEURNZDEURUSDGBPAUDGBPCADGBPCHFGBPJPYGBPNZDGBPUSDNZDCADNZDCHFNZDJPYNZDUSDUSDCADUSDCHFUSDJPY
2Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%Value%
3setting no.57287621172962311341144100114694771126275116
4
5Ratio of winning vs losing trades1.221.061.921.04#DIV/0!1.391.131.15#DIV/0!#DIV/0!1.34#DIV/0!#DIV/0!1.431.21#DIV/0!#DIV/0!1.581.571.10#DIV/0!#DIV/0!1.271.391.13#DIV/0!#DIV/0!1.13
6Days per trade cycle24143815#DIV/0!222313#DIV/0!#DIV/0!19#DIV/0!#DIV/0!2928#DIV/0!#DIV/0!304135#DIV/0!#DIV/0!14207#DIV/0!#DIV/0!7
7
8Profit trades3355%5251%2566%4951%0#DIV/0!3958%3453%6253%0#DIV/0!0#DIV/0!4357%0#DIV/0!0#DIV/0!3059%2955%0#DIV/0!0#DIV/0!3061%2261%2252%0#DIV/0!0#DIV/0!5756%4358%11353%0#DIV/0!0#DIV/0!11053%
9Loss trades2745%4949%1334%4749%0#DIV/0!2842%3047%5447%0#DIV/0!0#DIV/0!3243%0#DIV/0!0#DIV/0!2141%2445%0#DIV/0!0#DIV/0!1939%1439%2048%0#DIV/0!0#DIV/0!4544%3142%10047%0#DIV/0!0#DIV/0!9747%
10Total trades60100%101100%38100%96100%0#DIV/0!67100%64100%116100%0#DIV/0!0#DIV/0!75100%0#DIV/0!0#DIV/0!51100%53100%0#DIV/0!0#DIV/0!49100%36100%42100%0#DIV/0!0#DIV/0!102100%74100%213100%0#DIV/0!0#DIV/0!207100%
11
12Initial deposit (check)£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000£50,000
13Gross profit£96,87154%£130,89751%£89,44164%£116,48750%£0#DIV/0!£126,02258%£98,04752%£221,88952%£0#DIV/0!£0#DIV/0!£147,91957%£0#DIV/0!£0#DIV/0!£85,45758%£80,78154%£0#DIV/0!£0#DIV/0!£95,26660%£57,09761%£58,79351%£0#DIV/0!£0#DIV/0!£184,28155%£150,39257%£406,32353%£0#DIV/0!£0#DIV/0!£318,04753%
14Gross loss-£82,58446%-£123,89649%-£51,31536%-£115,40350%£0#DIV/0!-£91,17942%-£90,16648%-£203,50248%£0#DIV/0!£0#DIV/0!-£113,63443%£0#DIV/0!£0#DIV/0!-£61,28442%-£68,38646%£0#DIV/0!£0#DIV/0!-£63,23240%-£36,82639%-£55,38949%£0#DIV/0!£0#DIV/0!-£148,60445%-£111,22843%-£365,18147%£0#DIV/0!£0#DIV/0!-£285,11047%
15Total P & L£179,455100%£254,794100%£140,756100%£231,890100%£0#DIV/0!£217,201100%£188,213100%£425,390100%£0#DIV/0!£0#DIV/0!£261,554100%£0#DIV/0!£0#DIV/0!£146,740100%£149,167100%£0#DIV/0!£0#DIV/0!£158,498100%£93,923100%£114,183100%£0#DIV/0!£0#DIV/0!£332,886100%£261,620100%£771,504100%£0#DIV/0!£0#DIV/0!£603,158100%
16
17
18Ratio of profit vs loss1.171.061.741.01#DIV/0!1.381.091.09#DIV/0!#DIV/0!1.30#DIV/0!#DIV/0!1.391.18#DIV/0!#DIV/0!1.511.551.06#DIV/0!#DIV/0!1.241.351.11#DIV/0!#DIV/0!1.12
19Profit or loss£14,28729%£7,00114%£38,12676%£1,0852%£00%£34,84370%£7,88116%£18,38737%£00%£00%£34,28569%£00%£00%£24,17348%£12,39525%£00%£00%£32,03564%£20,27041%£3,4047%£00%£00%£35,67771%£39,16478%£41,14282%£00%£00%£32,93766%
20Account balance£64,287£57,001£88,126£51,085£50,000£84,843£57,881£68,387£50,000£50,000£84,285£50,000£50,000£74,173£62,395£50,000£50,000£82,035£70,270£53,404£50,000£50,000£85,677£89,164£91,142£50,000£50,000£82,937
21
22
23
24
25
ReportSingle Performance
Cell Formulas
RangeFormula
A1A1=COUNTA(B1:BE1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B20:BE20Cell Value=50000textNO
B20:XFD20Cell Value>50000textNO
B20:XFD20Cell Value<50000textNO
B5,D5,BD5,BB5,AZ5,AX5,AV5,AT5,AR5,AP5,AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5Cell Value<1textNO
B5,D5,BD5,BB5,AZ5,AX5,AV5,AT5,AR5,AP5,AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5Cell Value>1textNO
B5,D5,BD5,BB5,AZ5,AX5,AV5,AT5,AR5,AP5,AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5Cell Value=1textNO
B18,BD18,BB18,AZ18,AX18,AV18,AT18,AR18,AP18,AN18,AL18,AJ18,AH18,AF18,AD18,AB18,Z18,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18Cell Value<1textNO
B18,BD18,BB18,AZ18,AX18,AV18,AT18,AR18,AP18,AN18,AL18,AJ18,AH18,AF18,AD18,AB18,Z18,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18Cell Value>1textNO
B18,BD18,BB18,AZ18,AX18,AV18,AT18,AR18,AP18,AN18,AL18,AJ18,AH18,AF18,AD18,AB18,Z18,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18Cell Value=1textNO


Opps, Contents highlighted, copy and pasted.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,060
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Using Power Query/Get and Transform found on the DATA tab
Book6
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16Column17Column18Column19Column20Column21Column22Column23Column24Column25Column26Column27Column28Column29
2PairsEURUSDCADJPYGBPJPYGBPNZDAUDJPYCADCHFAUDCADGBPCHFEURNZDEURCHFAUDCHFNZDJPYNZDCHFCHFJPYAUDNZDUSDJPYNZDUSDEURJPYEURGBPEURCADUSDCHFUSDCADGBPAUDAUDUSDGBPCADNZDCADGBPUSDEURAUD
3setting no.10096947776725746443428272623216511111111111
4Total trades53643642386760495175101741021169620721300000000000
5Account balance62395.3957881.3470270.3953403.7388126.2784843.4364286.5382034.5474172.9184284.9757001.1689163.5885677.1368387.2851084.8182937.1191142.215000050000500005000050000500005000050000500005000050000
Sheet2


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"28", type text}, {"AUDCAD", type any}, {"Column1", type any}, {"AUDCHF", type any}, {"Column2", type any}, {"AUDJPY", type any}, {"Column3", type any}, {"AUDNZD", type any}, {"Column4", type any}, {"AUDUSD", type any}, {"Column5", type any}, {"CADCHF", type any}, {"Column6", type any}, {"CADJPY", type any}, {"Column7", type any}, {"CHFJPY", type any}, {"Column8", type any}, {"EURAUD", type any}, {"Column9", type any}, {"EURCAD", type any}, {"Column10", type any}, {"EURCHF", type any}, {"Column11", type any}, {"EURGBP", type any}, {"Column12", type any}, {"EURJPY", type any}, {"Column13", type any}, {"EURNZD", type any}, {"Column14", type any}, {"EURUSD", type any}, {"Column15", type any}, {"GBPAUD", type any}, {"Column16", type any}, {"GBPCAD", type any}, {"Column17", type any}, {"GBPCHF", type any}, {"Column18", type any}, {"GBPJPY", type any}, {"Column19", type any}, {"GBPNZD", type any}, {"Column20", type any}, {"GBPUSD", type any}, {"Column21", type any}, {"NZDCAD", type any}, {"Column22", type any}, {"NZDCHF", type any}, {"Column23", type any}, {"NZDJPY", type any}, {"Column24", type any}, {"NZDUSD", type any}, {"Column25", type any}, {"USDCAD", type any}, {"Column26", type any}, {"USDCHF", type any}, {"Column27", type any}, {"USDJPY", type any}, {"Column28", type any}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Column1] = "28" or [Column1] = "Account balance" or [Column1] = "setting no." or [Column1] = "Total trades")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","28","Pairs",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Transposed Table", each not Text.StartsWith([Column1], "Column")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Column2", Order.Descending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows")
in
    #"Transposed Table1"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,724
Members
414,013
Latest member
tnobbs

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
Top