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
Last edited by Shadmani; 08-26-2009 at 04:59 AM.
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
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
It works great !!!
I made a small change $c$3 instead of $c$1.
Thank you so much
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
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))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi JB,
You are a star !!!!!
Thank you. It works fantastic !!!
Kind Regards
Mani
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
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi JB,
Fantastic !!!!
Thank you. It is resolved.
Kind Regards
Shadmani
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks