how to count maximum number of lines repeated for a site
 

Site# Serial# No. of times repeated
205 12345 4
205 5678 5
205 54785 7
310 65432 4
310 47859 6

Could you suggest with any formula to count maximum number of times a serial
number has repeated.

For Eg:
Site # Serial#
205 54785
310 47859
In the above case, for site# 205 the maximum repeated serial# is 54785 and
for site# 310 the maximum repeated serial# 47859.

Suggest me with any formula to perform this work.

how to count maximum number of lines repeated for a site
 

One way: Copy your data to a new sheet then:

1. Data Sort by Site # (Asc) then by No. Repeated (Desc)
2. Data Filter Advanced Filter, List Range: A:A, Unique records
checked OK.

Then copy the selection to a new sheet to get the list of sites and
serial numbers with maximum number of repetitions.

On 19 Mar, 14:33, Igneshwara reddy
wrote:
Site# Serial# No. of times repeated
205 12345 4
205 5678 5
205 54785 7
310 65432 4
310 47859 6

Could you suggest with any formula to count maximum number of times a serial
number has repeated.

For Eg:
Site # Serial#
205 54785
310 47859
In the above case, for site# 205 the maximum repeated serial# is 54785 and
for site# 310 the maximum repeated serial# 47859.

Suggest me with any formula to perform this work.

how to count maximum number of lines repeated for a site
 

One way ..

Assuming source data in cols A to C, from row2 to row100 (say)

Site# is input in E2: 205, in E3: 310, etc

Place in F2, then array-enter (press CTRL+SHIFT+ENTER):
=INDEX($B$2:$B$100,MATCH(MAX(IF($A$2:$A$100=E2,$C$ 2:$C$100)),IF($A$2:$A$100=E2,$C$2:$C$100),0))
Copy F2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Igneshwara reddy" wrote:
Site# Serial# No. of times repeated
205 12345 4
205 5678 5
205 54785 7
310 65432 4
310 47859 6

Could you suggest with any formula to count maximum number of times a serial
number has repeated.

For Eg:
Site # Serial#
205 54785
310 47859
In the above case, for site# 205 the maximum repeated serial# is 54785 and
for site# 310 the maximum repeated serial# 47859.

Suggest me with any formula to perform this work.

how to count maximum number of lines repeated for a site
 

Hi Max,

Your formula worked.
Thanks for your suggestion.

Regards,
Igneshwara reddy

"Max" wrote:

One way ..

Assuming source data in cols A to C, from row2 to row100 (say)

Site# is input in E2: 205, in E3: 310, etc

Place in F2, then array-enter (press CTRL+SHIFT+ENTER):
=INDEX($B$2:$B$100,MATCH(MAX(IF($A$2:$A$100=E2,$C$ 2:$C$100)),IF($A$2:$A$100=E2,$C$2:$C$100),0))
Copy F2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Igneshwara reddy" wrote:
Site# Serial# No. of times repeated
205 12345 4
205 5678 5
205 54785 7
310 65432 4
310 47859 6

Could you suggest with any formula to count maximum number of times a serial
number has repeated.

For Eg:
Site # Serial#
205 54785
310 47859
In the above case, for site# 205 the maximum repeated serial# is 54785 and
for site# 310 the maximum repeated serial# 47859.

Suggest me with any formula to perform this work.

how to count maximum number of lines repeated for a site
 

Good to hear that.
You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Igneshwara reddy" wrote in
message ...
Hi Max,

Your formula worked.
Thanks for your suggestion.

Regards,
Igneshwara reddy