reporting module

[Shadmani]

Dear MOderators,
I have enclosed a simple spread sheet. The list will be long.

Sheet1 ( Will be ) is the sheet which will be data entered...datewise.

There will be another sheet ( Tailor made ) as Sheet2 ( Need )
The values from sheet1 need to be transfered to sheet2 across appropriate product & the date..

Can anyone help me ?

Kind Regards
Shadmani

[JBeaucaire]

Put this in C3 and copy down and across.

=INDEX('will be'!$C$1:$C$100, MATCH($B3 & C$2, INDEX('will be'!$A$1:$A$100 & 'will be'!$B$1:$B$100, 0), 0))

Once you've copied the formula everywhere, there will be errors where no match occurred. To eliminate those:

Highlight the whole dataset
Press F5, then click SPECIAL
Click Formulas and Errors (only, uncheck the rest)
Click OK
Press the Delete key

[Shadmani]

It works great !!!
I made a small change $c$3 instead of $c$1.

Thank you so much

[Shadmani]

Hi,

It was working on the example file as sent to you.
when I tried to implement on my works, it gives error.
I have enclosed the file for your reference...

I am trying to copy the qty comparing the style number & the avail date.

Please help..


Thanks
Mani

[JBeaucaire]

You had the MATCH() portion pointing at the wrong cells. In B4:

=INDEX(CPS!$L$2:$L$10, MATCH($A4 & B$3, INDEX(CPS!$A$2:$A$10 & CPS!$P$2:$P$10, 0), 0))

[Shadmani]

Hi JB,

You are a star !!!!!

Thank you. It works fantastic !!!

Kind Regards
Mani

[Shadmani]

Sorry dear...
One more question...

If I have same date for the same product, then the values should be added to the date on final sheet.

For example ( going back to my first example )
Washing machine 6, Oct - 10 pcs
Washing machine 6, Oct - 5 pcs.

Then my total pcs on my resultant sheet should show 15 pcs under 6th oct

Can you help me ?

Thanks
Shadmani

[JBeaucaire]

Going strictly by comparing CODES (A) and DATES (P) and summing QTY (L), you would need to use a SUMPRODUCT() formula to gather multiple values. This is much more costly calculation-wise, but still doable.

Put this in B4 and copy down and across:
=SUMPRODUCT(--(CPS!$A$2:$A$10=$A4), --(CPS!$P$2:$P$10=B$3), CPS!$L$2:$L$10)

One benefit of this approach is the #N/A errors go away, you'll now see zero in the cells with no matches. You can click on Tools > Options > View > [ ] Zero values (deselect that) to make the zeros vanish.

[Shadmani]

Hi JB,

Fantastic !!!!

Thank you. It is resolved.

Kind Regards
Shadmani