Vlookup & Average Across Multiple Sheets

[~Josh~]

Hey, new here, but already have found the forums and posts to be an excellent resource.

Goal: Create a list of average unit prices for multiple occurring items from multiple worksheets.

See attached xls file

The 'Average Unit Price' worksheet is a standard list of items that will never change.
The subsequent worksheets are from various projects with varying unit costs for the some of the standard items. In the future, additional projects will be included in the file as a new worksheet.

What I am trying to do:

Find all occurrences of the identifying item number from column A in sheet "Average Unit Price" in column A of all subsequent worksheets. There could be multiple occurrences in same worksheet.

Then, for those identified rows, return the Average of the value from column E (Average Unit Price).

Not sure if possible or where to start.

Thanks,

 

[p45cal]

I suspect if you have Power Query this might be relatively simple, however I may look into that later.
In the meantime, in the attached is a button (near cell F1 of the Average Unit Price sheet) which runs a macro to put the values in. It's very basic, no bells and whistles, no checks, t's not crossed and i's not dotted; consider it work in progress.
As well as put the averages in, it also puts a count of how many values went towards that average in the next column. Easy to stop that happening.
At the moment, I've got it to process only visible sheets between two new sheets I've added called Start and End. The idea being you put all sheets you want processed between those two. (If you know that you want ALL other sheets apart from Average Unit Price to be processed we can take these two new sheets out and use another, simpler method.)
One thing to note, is that sometimes the value in column E is zero. Viz.:
Average Unit Price D298 you might consider wrong because bid item no. 3123.0103 at cell E38 of 197.001 Park Vista - Bid No. 1 is 0 but is included in the average.
Average Unit Price D596 you might consider wrong because bid item no. 3305.0109 at cell E93 of 197.001 Park Vista - Bid No. 1 is 0 but is included in the average.
I've highlighted in yellow some of these cells.

What the code does is to make a single list of all the Bid item nos. and their prices from all the relevant sheets, in memory, it then goes through each of the values in column A of the Average Unit Price sheet, and looks for all similar values in the list and averages them.

 

[~Josh~]

I found this tutorial which is almost exactly what I was looking for. Took me all day to find thought. lol.

https://www.youtube.com/watch?v=wCE-oNGUmD4

 

[~Josh~]

I suspect if you have Power Query this might be relatively simple, however I may look into that later.
In the meantime, in the attached is a button whci runs a macro to put the values in. It's a very basic, no bells and whistles, no checks, ts not crossed and is not dotted; consider it work in progress.
As well as put the averages in, it also puts a count of values which went towards that average in the next column. Easy to stop that happening.
At the moment, I've got it to only process visible sheets between two new sheets I've added called Start and End. If you know that you want ALL other sheets apart from

Don't see any attachments...

 

[p45cal]

Yes, I accidentally pressed 'Post reply' while still composing. All finished now.

 

[~Josh~]

I suspect if you have Power Query this might be relatively simple, however I may look into that later.
In the meantime, in the attached is a button (near cell F1 of the Average Unit Price sheet) which runs a macro to put the values in. It's very basic, no bells and whistles, no checks, t's not crossed and i's not dotted; consider it work in progress.
As well as put the averages in, it also puts a count of how many values went towards that average in the next column. Easy to stop that happening.
At the moment, I've got it to process only visible sheets between two new sheets I've added called Start and End. The idea being you put all sheets you want processed between those two. (If you know that you want ALL other sheets apart from Average Unit Price to be processed we can take these two new sheets out and use another, simpler method.)
One thing to note, is that sometimes the value in column E is zero. Viz.:
Average Unit Price D298 you might consider wrong because bid item no. 3123.0103 at cell E38 of 197.001 Park Vista - Bid No. 1 is 0 but is included in the average.
Average Unit Price D596 you might consider wrong because bid item no. 3305.0109 at cell E93 of 197.001 Park Vista - Bid No. 1 is 0 but is included in the average.
I've highlighted in yellow some of these cells.

What the code does is to make a single list of all the Bid item nos. and their prices from all the relevant sheets, in memory, it then goes through each of the values in column A of the Average Unit Price sheet, and looks for all similar values in the list and averages them.

This is excellent. I think to get around the 0 issue, the data tabs need to be sure to delete any zero entries. Simple as that.

 

[p45cal]

I found this tutorial which is almost exactly what I was looking for. Took me all day to find thought. lol.

Your link doesn't show up, but it's this one I think:
https : //www . youtube .com/watch?v=wCE-oNGUmD4
(remove the spaces! It didn't show up when I tried either)

 

[p45cal]

I think to get around the 0 issue, the data tabs need to be sure to delete any zero entries. Simple as that.

Or change:
If ConsolidVals(1, i) = BidNo Then
in the code to:
If ConsolidVals(1, i) = BidNo And ConsolidVals(2, i) > 0 Then