CODEWITHSUNDEEP
excelvbaduplicatesgroupingformula
Omen
Omen

Reputation: 13

Determining a group of duplicates and finding its max/highest no

Unsure if the solution is VBA/Macro or a formula in excel. However the goal is copying Value of the Highest/Max Term # within a group of accounts having the same account name to it's corresponding AValue. [Image below]

Note: The accounts are already sorted and cannot be sorted to another format as it deals with 7000+ entries

screenshot

Upvotes: 1

Views: 3634

Answers (1)

Hamid Reza
Hamid Reza

Reputation: 485

You can solve your problem through 2 ways:

1: You can sort your data based on column A and column B both ascending. then you can insert the following formula in cell D3 and copy it down.

=IF(A3=A4;"";C3)

2: You can also use the following formula in cell D3 and copy it down:

{=IF(MAX($B$3:$B$15*IF($A$3:$A$15=A3;1;0)) = B3;C3;"")}

Note that first you must enter

=IF(MAX($B$3:$B$15*IF($A$3:$A$15=A3;1;0)) = B3;C3;"")

in cell D3 and after finishing the formula and while you are in edit mode, you must press Ctrl+Shift+Enter together. Excel automatically generate the above formula. If you write "{" and "}" manually, the formula won't work.

Upvotes: 1

Related Questions