Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 55
Default Assist with formula

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth







  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default Assist with formula

Assuming your data begins in A1 and is sorted as in your example:

In B1: =COUNTIF(A:A,A1)&" of "&A1

In B2: =IF(A2=A1,"",COUNTIF(A:A,A2)&" of "&A2)
Copy B2 down

Hope this helps,

Hutch

"Soth" wrote:

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth







  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Assist with formula

In B1: =IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1)&" of "&A1,"")

copy down as far as needed


"Soth" wrote:

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth







  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 55
Default Assist with formula

Great just what i'm looking for. You know, i''m interested in learning to
write...the If, the countif, and various charactor such as &, " etc..how you
came up with logic.

Can you write a plain english logical sens of this formula? just to help me
understand.

Thanks
Soth

"Teethless mama" wrote:

In B1: =IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1)&" of "&A1,"")

copy down as far as needed


"Soth" wrote:

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth







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
Formula to assist with counting dates Dilly Excel Discussion (Misc queries) 9 May 8th 09 06:25 PM
Formula to assist with Counting Dates Dilly Excel Discussion (Misc queries) 2 May 8th 09 04:01 PM
Need Assist With Formula Creation Rick Excel Worksheet Functions 3 December 28th 07 06:01 PM
Formula Assist [email protected] Excel Discussion (Misc queries) 3 June 7th 06 12:37 AM
Making Array Formula, please assist! Macinslaw Excel Worksheet Functions 3 October 11th 05 09:46 PM


All times are GMT +1. The time now is 10:18 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"