Solved: Only show maximum value of repeated data

[Dave T]

Hello All,

I have some Excel 2007 data from inspections.
Column A has a range of 'Plan Numbers' listed and column C has the number of inspections done for that particular Plan Number.

For example in the attached workbook there have been seven (7) inspections done on Plan Number 14.
I would like a formula that would only return the maximum number of the inspection for each of the plan numbers.

For example the result returned for Plan Number 14 would be 7. The preceding six cells for Plan Number 14 would be blank.

The formula should only return a value against the last inspection of any particular structure.

Any help would be appreciated.

Regards,
Dave T

[Trebor76]

Hi Dave,

Based on your comment "the formula should only return a value against the last inspection of any particular structure" use this formula in row 2 of whatever column you wish to display the results from...

=IF(A2=A3,"",C2)

...and fill down as required.

Note this may not actually be the maximum value it's just the last value and the data needs to be sorted in ascending sequence (as per your posted example) for it to work properly.

HTH

Robert

[Teeroy]

Hi Dave,

Instead of looking for the highest number just count the number of times the PN is repeated in column A e.g. for plan 14 use the formula "=COUNTIF(A2:A38,14)".

[snb]

forget column C and use this array formula from C2 downward


=IF($B2=MAX(($A$2:$A$100=$A1)*($B$2:$B$100));MAX(($A$2:$A$100=$A1)*($B$2:$B $100));"")

[shrivallabha]

forget column C and use this array formula from C2 downward


=IF($B2=MAX(($A$2:$A$100=$A1)*($B$2:$B$100));MAX(($A$2:$A$100=$A1)*($B$2:$B $100));"")
OP might have to replace ; with , as below:

=IF($B2=MAX(($A$2:$A$100=$A1)*($B$2:$B$100)),MAX(($A$2:$A$100=$A1)*($B$2:$B $100)),"")

[Dave T]

Hello All,

I really appreciate all of your responses, but I ended up with another solution that, whilst long winded, worked for me.

I sorted column A from smallest to largest and at the same time sorted column B largest to smallest.
I then used the 'Advanced Filter' to copy the unique values from column A to another worksheet (now I only had one number 14, etc.).
Using the unique values I used a VLOOKUP to return the data from the other columns in the worksheet that contained the duplicated values.

This worked well for me as the use of VLOOKUP will only return the first instance of the matching item. So... in my posted example, PN 14 then returned number 7 (as column B was sorted largest to smallest).

Might have been more work but I have not played with array formulas before and I know other people who might use the data would definitely have no idea of them and could very easily break them. At least they have some idea of how VLOOKUP works.

Once again I do appreciate all of your replies.

Regards,
David