Posts by QAM

Quote from Carim

Code

=SUMPRODUCT((RIGHT($A$2:$A$11,2)="RB")*VALUE((LEFT($A$2:$A$11,1))))

Thanks Carim -- there were some extra brackets around the LEFT function, but seems to work like a charm.

Cheers,

David

Heya,

I used to have a similar formula for what I want to accomplish at my previous job, but I cannot for the life of me remember.

I have a table with columns containing the following type of data

| January |
| 1RB |
| 1RB |
| 1RB |
| 1RB |
| 2RB |
| 1RB |
| 1RB |
| 1PR |
| 2PR |
| 1FO |

I'm looking for a formula that can sum the numerical values based on the two digit suffix. For the data above, the following results would be expect:

| RB | 8 |
| PR | 3 |
| FO | 1 |

I wish i knew how I did it previously but would really appreciate some help.

Thanks,

David

Re: Count of unique name and date of birth matches

All sorted now!!!

I created a duplicate table and removed duplicates then counted the DOBs.

Cheers

Re: Count of unique name and date of birth matches

Quote from NBVC;774942

=SUMPRODUCT(($C$2:$C$7=C2)/COUNTIFS($A$2:$A$7,$A$2:$A$7&"",$B$2:$B$7,$B$2:$B$7&"",$C$2:$C$7,$C$2:$C$7&""))

copied down

Thanks for the formula -- all it seemed to do was crash Excel unfortunately :?

Re: Count of unique name and date of birth matches

Hiya,

There can be multiple entries for the same person, hence rows 1 and 2 being the same, but as one unique birthday for that person.

Re: Unique ID formula from reverse date of birth

Thanks for the help!!

Hiya,

I have a spreadsheet of member details; names, contact details, date of birth.

The sheet will have multiple entries for the same person; and obviously, some dates of birth with will multiples.

I want to be able to count the of dates of birth for unique names. Example shows the count i would be expecting in column D.

| A | B | C | D |
| David | Jones | 22011986 | 01 |
| David | Jones | 22011986 | 01 |
| Paul | Harrison | 08091979 | 01 |
| Simon | Edgar | 10071980| 01 |
| Sam | Smith | 08091979 | 02 |
| Anthony | Jeffs | 22011986 | 02 |

I've racked by brains on an answer!

Any help??!

Hey all,

I've been trying to work on a project for my employer.

We are a member organisation where each membered account is given an internal ID, however, the same person can have multiple accounts.
We're trying to instigate a Unique ID which is individual to each separate person, use a reversed Date of Birth : YYYYMMDDXX

The XX suffix is dependent if the same date of birth recurs within the data set, but with a different user's name.

I've tried using Countif to try and work this out, but it seems to have an issue if a member has more than 3 different accounts.

Would anyone be able to help? I don't mind if the odd ID requires manual checking, but at the moment, I've got several hundred to review.

I've included a sample of my data.

Cheers,

David