Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
maximum repeated value markag Excel Worksheet Functions 2 June 16th 06 08:56 PM
Count the number of Lines in a "wrap text"-cell MichaelS_ Excel Discussion (Misc queries) 2 March 20th 06 09:15 AM
Does the Wrap function in excel allow a maximum number of lines? Redskins Fan Excel Discussion (Misc queries) 3 January 24th 06 07:11 PM
Count No. of times Dates are repeated Mandeep Dhami Excel Discussion (Misc queries) 6 December 8th 05 03:55 AM
how do I count the number of times a word is repeated in a range? sol Excel Discussion (Misc queries) 3 July 14th 05 01:53 PM


All times are GMT +1. The time now is 04:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"