Data collation killing me!!

[rhani111]

Ok...no answer to my last post so I will try again.....

I have a data page (10 of them in fact identical except the tab names), each of these has Column A as a description, Column B as subtotals of each division and columns C through to Z as the date.

I then have the unenviable task of trying to collate the data from dropdown boxes that the user selects the item number from, tabs to column C and types in the number purchased. Column B subtotals along the way.

How oh HOW can I do this three dimensional task as all 10 sheets need to be collated into ONE report???????

Please anybody got any ideas?? I've tryed VLOOKUP, HLOOKUP, MATCH but they will only look at one single line...and the item may have been used between two dates......and they both need to be individual as at the month end, the report uses dates to and from to calculate the usage.....

Gosh I'm even confusing myself now!!!

Thanks
Sandi

[VBA Noob]

Can you post an example ??

[rhani111]

umm...not sure how else to do it but ok...

Column A5:A20 Item Code
Column B21 Subtotal of items (changes as the dates changed and item used)
Column C1 through to Column Z1 is where the user enters the date the item was used
C100 Through to C150 is the NUMBER the user enters the times the item was used.....

This is repeated on 10 different sheets for different Months
I then need to report on the Item number and the number of times it was used between two dates. (a start and finish date.)

Does this make more sense?
Sandi

[Toppers]

...As I still have a problem visualising the data, can you post a sample w/book?

What's in columns C2 to C99 for example (may not be relevant but the layout
appears "strange" to me)?

"rhani111" wrote:

>
> umm...not sure how else to do it but ok...
>
> Column A5:A20 Item Code
> Column B21 Subtotal of items (changes as the dates changed and item
> used)
> Column C1 through to Column Z1 is where the user enters the date the
> item was used
> C100 Through to C150 is the NUMBER the user enters the times the item
> was used.....
>
> This is repeated on 10 different sheets for different Months
> I then need to report on the Item number and the number of times it was
> used between two dates. (a start and finish date.)
>
> Does this make more sense?
> Sandi
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=564594
>
>

[rhani111]

Ok...here's kinda what it looks like

[Toppers]

Sorry to be pedantic but I was hoping that you could give me (us!) a sample
WORKBOOK with good data coverage and (even better) include a sample of the
expected output.

"rhani111" wrote:

>
> Ok...here's kinda what it looks like
>
>
> +-------------------------------------------------------------------+
> |Filename: Testin.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=5089 |
> +-------------------------------------------------------------------+
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=564594
>
>

[rhani111]

I did send a working example...just click on the table in the centre of the document and choose workbook open...it should then open the table in excel and work...

[Toppers]

Attached is my attempt at answering your requirements (which I was still not quite clear about).

HTH

[Toppers]

..... sorry .. found an error: formula in Sheet1/3 should be as below:

=SUM(OFFSET($C$2,MATCH($A3,$A$3:$A$4,0),MATCH($A$9,$C$2:$Z$2,0)-1,1,MATCH($A$10,$C$2:$Z$2,0)-MATCH($A$9,$C$2:$Z$2,0)+1))

"Toppers" wrote:

>
> Attached is my attempt at answering your requirements (which I was still
> not quite clear about).
>
> HTH
>
>
> +-------------------------------------------------------------------+
> |Filename: testing.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5091 |
> +-------------------------------------------------------------------+
>
> --
> Toppers
> ------------------------------------------------------------------------
> Toppers's Profile: http://www.excelforum.com/member.php...o&userid=30076
> View this thread: http://www.excelforum.com/showthread...hreadid=564594
>
>

[rhani111]

That is awesome and you are really close....here is the dilemma, the subtotals are NOT used on the individual sheets for the between dates,
these are used for the whole month's total only. I have attached your sheet with what I need. I hope this is ok.....

I need about a three dimensional data collation, from what I have sent you..

Thanks so much for your help so far....!!!

[rhani111]

oooPS...forgot to attach it..... and can't seem to get it small enough in zip to be uploaded.......what the????

[Toppers]

....no attachment! Please re-post.

"rhani111" wrote:

>
> That is awesome and you are really close....here is the dilemma, the
> subtotals are NOT used on the individual sheets for the between dates,
> these are used for the whole month's total only. I have attached your
> sheet with what I need. I hope this is ok.....
>
> I need about a three dimensional data collation, from what I have sent
> you..
>
> Thanks so much for your help so far....!!!
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=564594
>
>

[rhani111]

SHeeeesh here it is....

[Toppers]

My brain hurts (still) trying to understand what is wanted!!!... so I'm going
to give it a rest. If you want to send a (partially) completed example of the
summary table using the data in Testing then maybe I'll understand better: in
particular expain how the dates are used in the Summary as there are no
obvious dates being compared. You say "summing only items that were used on
the same day"; if they weren't used, wouldn't value be zero anyway so does
this mean ignore ITEM CODES with zero usage? ... even more criteria!)

Foe example, what would you expect to see in summary table for "VK 8200 RAM"
as the dates on Sheet2 and Sheet3 are different?

You also mention another report ......... which sheet is it on?


[If this was my own project, I'd (almost certainly) use VBA to solve it].

Sorry to be so dim.


"rhani111" wrote:

>
> SHeeeesh here it is....
>
>
> +-------------------------------------------------------------------+
> |Filename: Testing.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5092 |
> +-------------------------------------------------------------------+
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=564594
>
>

[rhani111]

LOL...You aren't being dim, just I don't know how to explain it better.

The report sheet I deleted ,but added it to the bottom of your summary sheet. The dates are actually on the top of the report (start and end dates that is)...so the dates on the worksheets themselves need to be recorded then collated somewhere or somehow so that I have formulas that can look up the Item code, the date and the number used to place into the report.

Does that make better sense to you? You were on the right track with what you sent me, BUT the subtotals need to appear on the report NOT change on the Subtotal columns of each sheet.

Usually I would have no problem with this but due to them using dropdown lists for their item codes (and these are repeated elsewhere...sighhhh)...this is what is causing the problems. I normally would have just had a column for the item code to be entered, the date and the number (all three placed into another data page vertically and used a sumif statement!!) but this won't work if there is 24 columns across with different dates added over the 10 sheets (representing different months)....then just used advanced filter...but I can only get it to report on either the date and the total ON that date, or the item code and the number used...but NOT all three: i.e. DATE (total on each day), item code (by day also) and the total used for the date.

God now I've gone and done it again!!!!! I've confused myself.
thinking that sending you the whole document would have been easier, but considering it's over 100KB it won't send this way....and it's industry specific and i could be in for it.....

[Toppers]

If it is all possible to send me a sub-set of your "production" worksheet
(presumably not all months are reqired to show what is required) then it
MIGHT help! I am UK-based so if you are US-based it's unlikely I am going to
divulge any company secrets (although I understand the concern).

It's just I find it easier to work with the "actual" data so I can see how
things are calculated.

"rhani111" wrote:

>
> LOL...You aren't being dim, just I don't know how to explain it better.
>
> The report sheet I deleted ,but added it to the bottom of your summary
> sheet. The dates are actually on the top of the report (start and end
> dates that is)...so the dates on the worksheets themselves need to be
> recorded then collated somewhere or somehow so that I have formulas
> that can look up the Item code, the date and the number used to place
> into the report.
>
> Does that make better sense to you? You were on the right track with
> what you sent me, BUT the subtotals need to appear on the report NOT
> change on the Subtotal columns of each sheet.
>
> Usually I would have no problem with this but due to them using
> dropdown lists for their item codes (and these are repeated
> elsewhere...sighhhh)...this is what is causing the problems. I normally
> would have just had a column for the item code to be entered, the date
> and the number (all three placed into another data page vertically and
> used a sumif statement!!) but this won't work if there is 24 columns
> across with different dates added over the 10 sheets (representing
> different months)....then just used advanced filter...but I can only
> get it to report on either the date and the total ON that date, or the
> item code and the number used...but NOT all three: i.e. DATE (total on
> each day), item code (by day also) and the total used for the date.
>
> God now I've gone and done it again!!!!! I've confused myself.
> thinking that sending you the whole document would have been easier,
> but considering it's over 100KB it won't send this way....and it's
> industry specific and i could be in for it.....
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=564594
>
>

[rhani111]

Hi,

I am sending you copied and pasted data from the real workbook, with a description of what i need the formula to do.

It's just too hard to explain, the dates on Sheets 1,2 & 3 are never consecutive as they work a 4 day on/off roster. The item number from the drop down boxes need to be summarised on the summary sheet with the total used etc.....the start and end dates are used to count the number of items used each day (total to be recorded on the summary sheet as ONE single figure between these dates)....this make sense???

I have used countif and that will count the occurences of the item number, but not ADD them. Adding them is imperative as each sheet is actually for different sites and they may use the same item number at more than one site on the same day.....making better sense???

Thanks
Sandi

[Toppers]

Hi,
Attached is my solution. In each worksheet in column AA I have totalled the usage for each part for the given Start and End dates. The Start and End dates are now named ranges.

In the summary sheet, I total the columns AA from each sheet for each part.

The part number data validation is now a named range called Items.

Hopefully you can now customise this to your needs.

[rhani111]

Hi matey,

I have been trying your formula as it would relate to my workbook, but the formula for the subtotals between start and end dates returns a zero unless the exact dates are used. Is there a way to total them if the are Greater than or equal, less than or equal to the start and end dates???

Thanx
Rhani