Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
maximum repeated value | Excel Worksheet Functions | |||
Count the number of Lines in a "wrap text"-cell | Excel Discussion (Misc queries) | |||
Does the Wrap function in excel allow a maximum number of lines? | Excel Discussion (Misc queries) | |||
Count No. of times Dates are repeated | Excel Discussion (Misc queries) | |||
how do I count the number of times a word is repeated in a range? | Excel Discussion (Misc queries) |