• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data from another sheet [SOLVED]

P

pucha

Member
Dear friends


Thanks for the earlier problems that you all help me to solved.


I want to pull data from another sheet by matching the date.

Sheet 1, named as "dailymcx" & Sheet 2, named as "mcxall".


data in "dailymcx"

ALUMINI-I,08/01/2013,108.60,109.35,108.30,109.10,4.82,3672.00

ALUMINIUM-I,08/01/2013,108.70,109.35,108.25,109.10,2.86,2673.00

BRCRUDEOIL-I,08/01/2013,0.00,0.00,0.00,0.00,0.00,109.00

CARDAMOM-I,08/01/2013,673.00,676.40,649.00,653.40,3.49,2502.00

COPPER-I,08/01/2013,421.00,424.15,420.00,423.05,25.11,21806.00


data on "mcxall"

Ticker,Date,Open,High,Low,Close,d1,d2

ALUMINI-I 25/07/2013 107.2 107.2 104.9 105.5 0.23 40.38

ALUMINI-I 26/07/2013 105.25 105.35 103.8 104 0.16 32.11

ALUMINI-I 27/07/2013 103.95 104.3 103.9 104.2 0.08 34.13

ALUMINI-I 29/07/2013 104.35 104.65 103.95 104.45 0 36.78

ALUMINI-I 30/07/2013 104.65 105.9 104.3 105.5 -0.07 46.88

ALUMINI-I 31/07/2013 105.75 106.4 105.3 105.85 -0.12 49.88

ALUMINI-I 01/08/2013 108.6 109.35 108.3 109.1 -0.09 68.49

ALUMINIUM-I 02/07/2013 107 109 106.95 107.75 0.07 64.14

ALUMINIUM-I 03/07/2013 108.2 109.4 107.65 107.8 0.12 64.37

ALUMINIUM-I 04/07/2013 107.5 108.25 107.25 107.5 0.18 61.69


I want to match "Ticker & date" of "dailymcx" with "Ticker & date" of "mcxall".


As in sheet "mcxall" the "Tickers" are same like "ALUMINI-I" showing datas from "25/7/2013" to "01/08/2013". I want only the current date row value of "ALUMINI_I" from sheet "mcxall"


Thanks

Regards

Pucha
 
Hi, pucha!

And how will be the match performed? On what worksheet, which columns... Would you post or describe the desired output?

Regards!
 
Sorry for this...


I want to get the output data in sheet named "dailymcx", "H2".


Match should perform like this...


"Ticker & Date" of sheet "dailymcx" should match "Ticker & date" of sheet "mcxall" and the output in sheet "dailymcx", "h2"


Sorry for Inconvenient,


Regards
 
Hi, pucha!

In cell H2 of worksheet dailymcx don't you yet have a value? 3672, and 2673, 109, 2502, 21086, for the next rows. Should they be overwritten?

Regards!
 
Hi, pucha!


You have at least 2 choices:

1) Array formula

=SI.ERROR(INDICE(mcxall!A$2:H$11;COINCIDIR(A2&B2;mcxall!A$2:A$11&mcxall!B$2:B$11;0);3);"") -----> in english: =IFERROR(INDEX(mcxall!A$2:H$11,MATCH(A2&B2,mcxall!A$2:A$11&mcxall!B$2:B$11,0),3),"")

2) Normal formula

=SUMAPRODUCTO((mcxall!C$2:C$11)*(mcxall!A$2:A$11=A2)*(mcxall!B$2:B$11=B2)) -----> in english: =SUMPRODUCT((mcxall!C$2:C$11)*(mcxall!A$2:A$11=A2)*(mcxall!B$2:B$11=B2))


In the examples I retrieved the value of column C (or 3rd), adjust that properly to fit your needs.


Just advise if any issue.


Regards!
 
Hi, pucha!

It's strange, they're both working for me. Give a look to this file:

https://dl.dropboxusercontent.com/u/60558749/Data%20from%20another%20sheet%20%28for%20pucha%20at%20chandoo.org%29.xlsx

Regards!


EDITED


PS: Array INDEX/MATCH at column I and normal SUMPRODUCT at column J.
 
Dear SirJB7


Due to internet disturbances I could not reply.


Thanks its working.


But till not getting the output as I required, may be due to incomplete data I have provided in my first post. So please requested to check that.

=================================================================

I am giving the data once again as there was some mistake above

===================================================================

data in "dailymcx"----Heading

Ticker,Date,Open,High,Low,Close,vol,oi (which i forgot to write the heading above)


ALUMINI-I,08/01/2013,108.60,109.35,108.30,109.10,4.82,3672.00

ALUMINIUM-I,08/01/2013,108.70,109.35,108.25,109.10,2.86,2673.00

BRCRUDEOIL-I,08/01/2013,0.00,0.00,0.00,0.00,0.00,109.00

CARDAMOM-I,08/01/2013,673.00,676.40,649.00,653.40,3.49,2502.00

COPPER-I,08/01/2013,421.00,424.15,420.00,423.05,25.11,21806.00


data on "mcxall"-----Heading

Ticker,Date,Open,High,Low,Close,d1,d2,d3,d4(heading d3 & d4 was not added above)


ALUMINI-I 25/07/2013 107.2 107.2 104.9 105.5 0.23 40.38 buy buy

ALUMINI-I 26/07/2013 105.25 105.35 103.8 104 0.16 32.11 buy buy

ALUMINI-I 27/07/2013 103.95 104.3 103.9 104.2 0.08 34.13 sell buy

ALUMINI-I 29/07/2013 104.35 104.65 103.95 104.45 0 36.78 sell sell

ALUMINI-I 30/07/2013 104.65 105.9 104.3 105.5 -0.07 46.88 sell buy

ALUMINI-I 31/07/2013 105.75 106.4 105.3 105.85 -0.12 49.88 buy buy

ALUMINI-I 01/08/2013 108.6 109.35 108.3 109.1 -0.09 68.49 buy sell

ALUMINIUM-I 02/07/2013 107 109 106.95 107.75 0.07 64.14 sell sell

ALUMINIUM-I 03/07/2013 108.2 109.4 107.65 107.8 0.12 64.37 buy buy

ALUMINIUM-I 04/07/2013 107.5 108.25 107.25 107.5 0.18 61.69 sell sell


Please check this data of the two sheets.


I check the file you uploaded. every thing is working fine.


But as the formula is of sum-products the last cell "d3" (which is in alphabet) is not achieved, as it shows "#Value!" .


I want the output from d1 to d4 of sheet mcxall into sheet dailymcx "I2", "J2" etc.

Please rectify the formula.


Thanks

Regards!
 
Hi ,


Can you say why the date format in the two sheets is different ? In the dailymcx tab , it is mm/dd/yyyy , whereas in the mcxall tab , it is dd/mm/yyyy.


Which format is your computer's native format ?


Narayan
 
Hi ,


In that case , use the following formula , entered as an array formula , in I2 , and copy across and down :


=IFERROR(INDEX(mcxall!G$2:G$11,MATCH($A2&$B2,mcxall!$A$2:$A$11&mcxall!$B$2:$B$11,0)),"")


Of course , this will work only if the dates which are in dd/mm/yyyy format are manually changed to the mm/dd/yyyy format. Will you do this ?


If this cannot be done , then the above formula will have to be revised.


Narayan
 
Hi

Thanks


I have change manually the date format to mm/dd/yyyy & copied the formula to I2 But it showing "#NAME?" in that cell.


Please Revised


Regards

Pucha
 
Hi Pucha ,


It is possible the IFERROR function is not available in your version of Excel ; can you try this , and give feedback ?


=IF(ISERROR(MATCH($A2&$B2,mcxall!$A$2:$A$11&mcxall!$B$2:$B$11,0)),"",INDEX(mcxall!G$2:G$11,MATCH($A2&$B2,mcxall!$A$2:$A$11&mcxall!$B$2:$B$11,0)))


Enter this as an array formula , using CTRL SHIFT ENTER.


Narayan
 
You must log in or register to reply here.
Back
Top