How to fill up date wise data with repeated entries?

[Nilesh Karhade]

Hi All! M currently workin on Mgmt Dashboard. I hav been using lots of tricks n shortcuts frm this forum for my Dshbd (Thanks a lot!! Its really a GGGreat Help !)


Its been 2 days now, m struggling on a compact Table. I hav to pick up data from a raw sheet & fill it up in my Compact Table (which has many features) But the problem is picking data is somewhat comlex. see below.


Raw Sheet


M/C No 3207 3510 1750 3343 3207 3143 Continue....

Prod. Qty. 350 500 210 1000 1200 10

Prod. Date 8-Jul 8-Jul 8-Jul 10-Jul 17-Jul 18-Jul


I hav to pick up Prod. Qty frm Raw Sheet for each Machine (M/C) & fill it up in follwng formt:


Reqd Format


Week I

M/C No. 1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul 7-Jul (Continue...)

3207

3510

1750

3222

3531

3343


You mite hav noticed, Machines are repeating (e.g.-3207, on 8 & 17 jul)Actually there is plenty of repetition!


Well, now I can tell u wht I m trying to do.


1. Select 1st M/C i.e. 3207 frm Reqd Frmt sheet, search it in Raw Sheet

2. Wherever it is found, check prod. dates & go to corresponding dates in Reqd. Formt sheet.

3. Now, Paste the Prod. Qty in front of respective Dates & Machine.


Well, M struggling for ths part. I tried VLOOKUP, HLOOKUP, MATCH, OFFSET, IF in several combinations.


Plez suggest me, how can it be done?

Thanx!

 

[Hui]

Nilesh

I have assumed that

M/C No is C1:H1

Prod. Qty. is C2:H2

Prod. Date is C3:H3


M/C listed in B7:B12

Dates in C6:N6


Put

=SUMPRODUCT(1*($C$1:$H$1=$B7)*($C$3:$H$3=C$6),($C$2:$H$2))

Into C7 and copy across and down

Adjust Ranges to suit

 

[Nilesh Karhade]

Hey.. Thanx..!

I dnt knw how did it work, bt its showing results. Jst some data is missing in final results (e.g. - not showing 2nd entry of 3207 on 17th July). M verifying if I m going wrong somewhere or any rectification in formula is require, dnt knw!

Anywy Thnx...!

 

[Hui]

It works for me

Do you have a date of 17/7 above a column?

Make sure the cell where 17 July is reads 40376.000 when you change it to Comma Format, ie it should have no values after the decimal


For how Sumproduct works have a read of:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

or

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

 

[Nilesh Karhade]

Hey, Great!! Its Perfect..!

I tried this function on a fictious data. It works amazingly...!

There might b some problem about uniformity of data in my Raw Sheet. I'll try again by 2maro. Its my leaving time.

Thanks a lot again! (You saved my next 2 more days..!)

Great Work..!