Windows 10 Excel 2019
I have a workbook with two worksheets, Product Data and Product List. The data in Product List is generated each time the formula I am looking for is run.
The number of products is unknown/unlimited
The number of rows for each product is unknown/unlimited
Each work sheet has a header row
In the Product Data page...
Columns A, B, J, K, will have duplicate date that should be used to identify the rows that relate to each product.
Columns G & H also relate to each product but will contain a mix of values and is not used to identify the products.
For each product (identified by matching data in Product List sheet Columns A, B, J, K,) I need excel to look at Column G and return the number that appears the most in each matching set of data, maybe '=MODE.SNGL(G2:G) down to the end of the matches.
The code/formula I need will do the following
When the first set of matched values have been processed (in yellow), it then moves on and processes the next matched set (green),
and then the next (blue) and so on all of the way down the Product List Sheet and copies it into the Product List page (example further down).
I have the following VBA which pulls the data from the Products Date worksheet but I can not work out how insert '=MODE.SNGL(G2:G) or something like it into the code of Column G range so it loops and starts new for each matched set of data.
' Starting with cell A2 through to cell G2, and down to row 40,000, this inserts the forumlas to ' pull the data from the worksheet "Track Data2
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK('Track Data'!RC), """",IF(OR('Track Data'!RC[8]=""Wav"",'Track Data'!RC[8]=""Flac"",'Track Data'!RC[8]=""Mp3"",'Track Data'!RC[8]=""Aif"",'Track Data'!RC[8]=""OSX AU""),'Track Data'!RC,""""))"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",'Track Data'!RC)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",'Track Data'!RC[8])"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",'Track Data'!RC)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]="""","""",'Track Data'!RC[2])"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]="""","""",'Track Data'!RC[])"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]="""","""",'Track Data'!RC[2])"
Range("A2:G2").Select
Selection.AutoFill Destination:=Range("A2:G40000"), Type:=xlFillDefault
Range("A2:G40000").Select
Application.GoTo Reference:="R2C1"
An example of the Products Data worksheet.
PRODUCT TYPE DATA 1 DATA 2 DATA 3 DATA 4 NO 1 N0 2 DATA 5 PRICE PACK TYPE
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 1058 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 1058 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 1058 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 4 1058 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 4 652 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 2 331 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 2012 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 1058 Data 5 22.99 Tin
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 1058 Data 5 22.99 Tin
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 300 442 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 43 442 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 300 442 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 21 5678 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 14 442 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 62 789 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 300 442 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 300 1233 Data 5 9.89 Tube
Yellow Paint Oil Data 1 Data 2 Data 3 Data 4 300 442 Data 5 9.89 Tube
Black Paint Powder Data 1 Data 2 Data 3 Data 4 1 3 Data 5 6.28 Tin
Black Paint Powder Data 1 Data 2 Data 3 Data 4 1 45 Data 5 6.28 Tin
Black Paint Powder Data 1 Data 2 Data 3 Data 4 2 20 Data 5 6.28 Tin
Black Paint Powder Data 1 Data 2 Data 3 Data 4 3 3 Data 5 6.28 Tin
Black Paint Powder Data 1 Data 2 Data 3 Data 4 3 8 Data 5 6.28 Tin
Black Paint Powder Data 1 Data 2 Data 3 Data 4 6 3 Data 5 6.28 Tin
Black Paint Powder Data 1 Data 2 Data 3 Data 4 1 3 Data 5 6.28 Tin
An example of how the Product List page will look.
PRODUCT TYPE DATA 1 DATA 2 DATA 3 DATA 4 M. DUPS No 2 DATA 5 PRICE PACK TYPE
Red Paint Powder Data 1 Data 2 Data 3 Data 4 12 1058 Data 5 22.99 Tin
Yellow PaintOil Data 1 Data 2 Data 3 Data 4 300 442 Data 5 9.89 Tube
Black Paint Powder Data 1 Data 2 Data 3 Data 4 1 3 Data 5 6.28 Tin
