Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.

Event2020 · Mar 21, 2024

I need a formula that 1st. Identifies each set of matching data working down the sheet (dynamically) and then looks at G2 down and in each set of date returns the duplicate number that appears the most and then does the same for Column H, all the way down the whole sheet until there is no more data.

The formula should be VBA if possible.

Columns G & H are not included in the set matching criteria as they do not have matching numbers and could be any combination of numbers.

In the xl2bb examples. the first one is sheet 1 (product List) of the work book and
the second is Sheet 2 (Product Data).

I also include a link to the workbook here
Example Workbook

Looking at my example, for
Red Paint that number would be Column G = 12 and Column H = 1058
Yellow Paint that would be Column G = 300 and Column H = 442
Black Paint that would be Colum G = 1 and Column H = 3

I have the VBA code below that looks at Track Sheet, Column A and if it is not null it then pulls the value from the same position on the Track Data sheet.

VBA Code:

Range("G2").Select                                        
ActiveCell.FormulaR1C1 = "=IF(RC[-6]="""","""",'Product Data'!RC)"                                        
                                        
Range("H2").Select                                        
ActiveCell.FormulaR1C1 = "=IF(RC[-7]="""","""",'Product Data'!RC)"

However, I am unable to workout how to have the formula identify and return the duplicates that appear the most in each set of data as I have already described.

Thank you in advance everyone.

Product List work sheet

Example.xlsx

A

B

C

D

E

F

G

H

I

J

K

1

Product

Product Type

Data 1

Data 2

Data 3

Data 4

Number 1

Number 2

Data 5

Price

PACK TYPE

2

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

1058

Data 5

22.99

Tin

3

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

300

442

Data 5

9.89

Tube

4

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

1

3

Data 5

6.28

Tin

5

6

I need a formula that 1st. Identifies each set of matching data working down the sheet (dynamically) and then looks at G2 down and in each set of date returns the duplicate number that appears the most

7

and then does the same for Column H, all the way down the whole sheet until there is no more data.

8

9

The formula could be entered into each Cell in Columns G & H or it could be VBA

10

Columns G & H are not included in the set matching criteria as they do not have matching numbers and could be any combination of numbers.

11

12

Looking at my example, for

13

Red Paint that number would be Column G = 12 and Column H = 1058

14

Yellow Paint that would be Column G = 300 and Column H = 442

15

Black Paint that would be Colum G = 1 and Column H = 3

16

17

I have the VBA code below that looks at Track Sheet, Column A and if it is not null it then pulls the value from the same position on the Track Data sheet.

18

19

Range("G2").Select

20

ActiveCell.FormulaR1C1 = "=IF(RC[-6]="""","""",'Product Data'!RC)"

21

22

Range("H2").Select

23

ActiveCell.FormulaR1C1 = "=IF(RC[-7]="""","""",'Product Data'!RC)"

24

25

However, I am unable to workout how to have the formula identify and return the duplicates that appear the most in each set of data as I have

26

already described.

Product List

Product Data work sheet

Example.xlsx

A

B

C

D

E

F

G

H

I

J

K

1

Product

Product Type

Data 1

Data 2

Data 3

Data 4

Number 1

Number 2

Data 5

Price

PACK TYPE

2

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

1058

Data 5

22.99

Tin

3

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

1058

Data 5

22.99

Tin

4

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

1058

Data 5

22.99

Tin

5

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

4

1058

Data 5

22.99

Tin

6

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

4

652

Data 5

22.99

Tin

7

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

2

331

Data 5

22.99

Tin

8

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

2012

Data 5

22.99

Tin

9

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

1058

Data 5

22.99

Tin

10

Red Paint

Powder

Data 1

Data 2

Data 3

Data 4

12

1058

Data 5

22.99

Tin

11

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

300

442

Data 5

9.89

Tube

12

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

43

442

Data 5

9.89

Tube

13

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

300

442

Data 5

9.89

Tube

14

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

21

5678

Data 5

9.89

Tube

15

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

14

442

Data 5

9.89

Tube

16

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

62

789

Data 5

9.89

Tube

17

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

300

442

Data 5

9.89

Tube

18

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

300

1233

Data 5

9.89

Tube

19

Yellow Paint

Oil

Data 1

Data 2

Data 3

Data 4

300

442

Data 5

9.89

Tube

20

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

1

3

Data 5

6.28

Tin

21

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

1

45

Data 5

6.28

Tin

22

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

2

20

Data 5

6.28

Tin

23

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

3

Data 5

6.28

Tin

24

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

3

8

Data 5

6.28

Tin

25

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

6

3

Data 5

6.28

Tin

26

Black Paint

Powder

Data 1

Data 2

Data 3

Data 4

1

3

Data 5

6.28

Tin

27

28

The data is entered on this page.

Product Data

shift-del · Mar 22, 2024

A PQ solution.

Query "ProductData"

SQL:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Product Type", type text}, {"Data 1", type text}, {"Data 2", type text}, {"Data 3", type text}, {"Data 4", type text}, {"Number 1", Int64.Type}, {"Number 2", Int64.Type}, {"Data 5", type text}, {"Price", type number}, {"PACK TYPE", type text}})
in
    #"Changed Type"

Query "Result"

SQL:

let
    Source = ProductData,
    #"Grouped Rows" = Table.Group(Source, {"Product", "Product Type", "Data 1", "Data 2", "Data 3", "Data 4", "Data 5", "Price", "PACK TYPE"}, {{"Number 1", each List.Mode([Number 1]), type nullable number}, {"Number 2", each List.Mode([Number 2]), type nullable number}})
in
    #"Grouped Rows"

Event2020 · Mar 22, 2024

shift-del said:

A PQ solution.

Query "ProductData"

SQL:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Product Type", type text}, {"Data 1", type text}, {"Data 2", type text}, {"Data 3", type text}, {"Data 4", type text}, {"Number 1", Int64.Type}, {"Number 2", Int64.Type}, {"Data 5", type text}, {"Price", type number}, {"PACK TYPE", type text}})
in
    #"Changed Type"

Query "Result"

SQL:

let
    Source = ProductData,
    #"Grouped Rows" = Table.Group(Source, {"Product", "Product Type", "Data 1", "Data 2", "Data 3", "Data 4", "Data 5", "Price", "PACK TYPE"}, {{"Number 1", each List.Mode([Number 1]), type nullable number}, {"Number 2", each List.Mode([Number 2]), type nullable number}})
in
    #"Grouped Rows"

Hi

Thank you for your reply and suggestion which I appreciate.

How do I incorporate your PQ solution into the VBA code formulas that I provided?

The VBA is very important as the whole work book is functioning by using VBA.

Many thanks

shift-del · Mar 22, 2024

PQ code is totally different code then VBA code.

You may have to wait for a helper with VBA expertise.

Event2020 · Mar 22, 2024

Many thanks Shift-Del

Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.

Event2020

Board Regular

Excel Facts

shift-del

Well-known Member

Event2020

Board Regular

shift-del

Well-known Member

Event2020

Board Regular

Similar threads

Forum statistics

Share this page

Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.

Event2020

Board Regular

Excel Facts

shift-del

Well-known Member

Event2020

Board Regular

shift-del

Well-known Member

Event2020

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock