Hey guys,
I am dealing with 10'000 plus lines and need to get a rolling 12 month average for items on another sheet, however I cannot interact with the other sheet due to the data overwrighting previous data daily.
With the averages I am trying to use the month, item and company to lookup using the pre made CAT that comes with the report sheet, however I cannot get the averages without a vlookup since INDEX and MATCH don't seem to get same or similar values properly and don't seem to work with what I am trying to do.
I have tried using AVERAGEIF however it doesn't seem to work with vlookup, and AVERAGEIF also doesn't take into account several entry matches.
My english isn't fantastic so I have put the 2 sheets down below, first one is the report I am trying to build and 2nd is a sample of the constantly extracted data (cannot attach sheet for some reason, forum not allow that?) (below is tab delimited)
Sheet1:
Monthly Average Sales
Business Item Number(hidden later) January February March
(uses unique function, removed for testing sake)
hals paints 1025L INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
premium supplier 1025L INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
All Trace 1025L INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
Safety Industries 1025L INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
Murfies 1025L INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
hals paints B2000 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
premium supplier B2000 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
All Trace B2000 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
Safety Industries B2000 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
Murfies B2000 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
hals paints B2010 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
premium supplier B2010 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
All Trace B2010 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
Safety Industries B2010 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
Murfies B2010 INSERT AVERAGE HERE INSERT AVERAGE HERE INSERT AVERAGE HERE
sheet 2:
Month Business Item CAT Item Total
January hals paints 1025L Januaryhals paints1025L 2
January premium supplier B2000 Januarypremium supplierB2000 6
January All Trace 1025L JanuaryAll Trace1025L 2
February Safety Industries B2000 FebruarySafety IndustriesB2000 2
February Murfies B2000 FebruaryMurfiesB2000 4
February hals paints 1025L Februaryhals paints1025L 5
March premium supplier B2000 Marchpremium supplierB2000 1
March All Trace 1025L MarchAll Trace1025L 2
March All Trace 1025L MarchAll Trace1025L 1
Bookmarks