Hello,
I have a mailing job that needs to be done. I need to seperate the letters by the number of pages for each account that letter will be mailed to. I extracted data out of the report file to give me the account numbers and the number of pages for each account number as below:
AcctNbr Pages
123456 Page 1 of 2
123456 Page 2 of 2
456789 Page 1 of 2
456789 Page 2 of 2
444444 Page 1 of 1
555555 Page 1 of 3
555555 Page 2 of 3
555555 Page 3 of 3
987654 Page 1 of 4
987654 Page 2 of 4
987654 Page 3 of 4
987654 Page 4 of 4
I need a formula or way so that I can have the account number listed once along with the total number of pages for that account like below:
AcctNbr PageCount
123456 2
456789 2
444444 1
555555 3
987654 4
This way I can then sort by Page Count. I have also attached an example spreadsheet. Any help with this will be greatly appreciated. I haven't had to do much Excel work in quite a while and have forgotten quite a bit.
Thank you,
Dave
You can use the Right() function to get the number of pages in each account. Then copy and paste the function to a value, and filter unique to get each account number once.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
I figured it out myself. I used a simple COUNTIF formula and then ran a piece of VBA code that I have to remove the duplicates. But I appricate any replies that came my way. These forums are always very helpful.
Thank you,
Dave
Try this
In D2
In E2=IFERROR(LOOKUP(999^99,CHOOSE({1;2},"",INDEX($A$2:$A$30,MATCH(TRUE,INDEX(ISNA(MATCH($A$2:$A$30,$D$1:$D1,0)),0),0)))),"")
Drag/Fill Both Down=IF(D2="","",COUNTIF(A:A,D2))
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks