Need to find different values against same name

Hi there. this can be achieved using this sort of array formula. This one returns the dates. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Formula:

Please Login or Register  to view this content.

See sheet for exact formulae used for each of the thhree columns

Don't type the curly braces yourself - it won't work...

Next time, PLEASE attach a SAMPLE sheet, not one with two MASSIVE sheets. That is totally unnecessary.

F2: "October"
F3: "November"

G2: =COUNTIF('DATA(October)'!C:C, RESULT!E4)
G3: =COUNTIF('DATA (November)'!C:C, RESULT!E4)

D5: =IF($C5>SUM($G$2:$G$3),"",IF($C5<=$G$2,INDEX('DATA(October)'!B:B,MATCH($E$4,'DATA(October)'!$C:$C,0)+ROW(A1)-1),INDEX('DATA (November)'!B:B,MATCH($E$4,'DATA (November)'!$C:$C,0)+ROW(A1)-$G$2-1)))

Copy D5 down and across through F103.

Originally Posted by Glenn Kennedy

Next time, PLEASE attach a SAMPLE sheet, not one with two MASSIVE sheets. That is totally unnecessary.

An attached file in the *.xlsb format that is 1.29mb in size should give you an indication that the file is probably huge!

I usually won't download a file if it's bigger than 15-20 kb in *.xlsx format.

Exactly..... I'm paying roaming charges at the moment and i didn't see the size of this file until it was too late....

Originally Posted by Glenn Kennedy

Next time, PLEASE attach a SAMPLE sheet, not one with two MASSIVE sheets. That is totally unnecessary.

good advice... thanks Glenn.

Dear JBeaucaire,

I am very much pleased with your solution and it works perfectly but due to my little knowledge, I can not customize the formula when more sheets are added. As the file is more than 1.2 mbs, so I am attaching an image file. I am eagerly waiting for your kind response. Please note that I have to add new sheet (month-wise) every month. Should I change the formula every month?

With thanks,
Samorita

Dear Glenn Kennedy,

Your formula works great but the problem arises when I add more sheet. Would you please have a look on the image file I have attached with JBeaucaire's reply. I am sorry that you had to pay extra roaming charge for helping me. And this time I have not attached large file, a simple image. Waiting for your kind help.

With regards,
Samorita

Unfortunately, some of us (me included) cannot see your iages at all!!

So, a SMALL sample file is to be greatly preferred. Will you be wanting a fixed number of sheets, or an ever expanding list? I think that this is very quickly going to get too complicated for a formual-based solution and you might need to consider a VBA solution.

No formula based solution will survive endless addition of new sheets. Only VBA can do that.

Glenn, tip of the day, I'm no fan of pics either, but when I really want to see one, I just open the page in Chrome instead of IE.

I use Firefox and have never had any problems until the new laptop decided that it wouldn't let me see images, even though i have specifically allowed the use of images from this site.

Originally Posted by JBeaucaire

No formula based solution will survive endless addition of new sheets. Only VBA can do that.

Glenn, tip of the day, I'm no fan of pics either, but when I really want to see one, I just open the page in Chrome instead of IE.

Dear JBeaucaire,

I am sorry that you can not see the image file. Your formula works great for two sheets (reference to your early post). Hence, my question is, How will I customize the formula when I have data in 3 or 4 sheets?, i,e. DATA(October), DATA (November), DATA (December), DATA (January). I have tried to do but I have failed. It does not return the correct result. It would be a great help to me if you customize the formula for me. Please help this friend.

I have tried to customize your formula by the following way:

F2: "October"
F3: "November"
F4: "December"
F5: "January"

G2: =COUNTIF('DATA(October)'!C:C, RESULT!E4)
G3: =COUNTIF('DATA (November)'!C:C, RESULT!E4)
G4: =COUNTIF('DATA (December)'!C:C, RESULT!E4)
G5= COUNTIF('DATA (January)'!C:C, RESULT!E4)

D5: =IF($C5>SUM($G$2:$G$3),"",IF($C5<=$G$2,INDEX('DATA(October)'!B:B,MATCH($E$4,'DATA(October)'!$C:$C,0)+ROW(A1)-1),IF($G$2<=$G$3,INDEX('DATA (November)'!B:B,MATCH($E$4,'DATA (November)'!$C:$C,0)+ROW(A1)-$G$2-1), IF($G$4=($G$2+$G$3),INDEX('DATA (December)'!B:B,MATCH($E$4,'DATA (December)'!$C:$C,0)+ROW(A1)-$G$2-1), IF($G$5<=($G$2+$G$3+$G$4),INDEX('DATA (January)'!B:B,MATCH($E$4,'DATA (January)'!$C:$C,0)+ROW(A1)-$G$2-1)))))))

Copy D5 down and across through F103.

With thanks,
Samorita

See if this makes it a little easier to read:

Please Login or Register  to view this content.

The I was able to help with the two sheet problem correctly because you provided a working two-sheet workbook.

If there are 4 sheets to deal with, your small sample workbook uploaded here should have the 4 sheets. You would never upload a 10mb anything, right? Just a smaller desensitized version that allows the problem to be examined.

Originally Posted by JBeaucaire

If there are 4 sheets to deal with, your small sample workbook uploaded here should have the 4 sheets. You would never upload a 10mb anything, right? Just a smaller desensitized version that allows the problem to be examined.

Dear JBeaucaire,

This is a 1.5 mbs file of 6 sheets. Most of the stocks are omitted from the sheets but "ABBANK" is in every sheet. Hope this will work. Waiting for your response, friend. If it is still huge, I can delete more stocks to reduce the file size.

With regards,
Samorita

Not going to work any more on a formula that just doesn't expand with additional sheets. Here's a VBA solution that should work for any number of sheets.

1) Right-click the RESULTS sheet tab and select VIEW CODE

2) Paste in this macro to the window that appears:

Please Login or Register  to view this content.

3) Close the VBEditor

4) Make an edit in E4....


NOTE: If you ever want to bring back more columns of data, adjust the two parts marked in red.

Dear JBeaucaire,

Thanks is not enough for such a helping friend like you. Your Vba works excellently. You have spent lots of time to help me. I will never forget your contribution. I pray to God for you. And one final request, if possible, please modify the macro for me to draw all headings, "DATE", "TRADING CODE", "LTP*", "HIGH", "LOW", "OPENP*", "CLOSEP*", "YCP", "TRADE", "VALUE (mn)", "VOLUME". No more request about this thread.

Please be happy always.

With regards,
Your friend, Samorita

The macro is intended to UPDATE the sheet you already had laid out. So the headers must be there from the start.

NOTE: PLease don't use the "Reply with Quote." Just use "Reply." Thanks.

Check post #20 again. I marked two things in red. Adjust those if you're saying you want to bring back MORE columns of data.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.