Index formula, duplicate records problem

[masond3]

I Need to know

how many unique corpid (column A) there are,
how many unique chainid (column b) there are,
how many unique externalid (column d) there are,

I also need to know how many externalid (column d) go in to chained (column b) and how many chained go into corpid (column A)


I have created something in access, which works okay, however i need it in excel

please find attached screen prints of access
unfortunately it wouldn’t allow me to attached the access file. But i have put it into excel so hopefully that it explains it abit better

[masond3]

Can any body help ?

[masond3]

Anyone got any ideas

[Haseeb Avarakkan]

Try this to get Unique.

=SUM(IF(FREQUENCY(IF('Data'!A2:A1480<>"",MATCH('Data'!A2:A1480,'Data'!A2:A1480,0)),ROW('Data'!A2:A1480)-ROW('Data'!A2)+1),1))

Confirmed with CTRL+SHIFT+ENTER, rather than just ENTER.

If you can convert the column to valid numbers, select Column go to Data >> text to columns >> Finish. Then format the cell as Number. Then you can use formula with just ENTER.

=SUM(SIGN(FREQUENCY('Data'!A:A,'Data'!A:A)))

Change the column for others.

[masond3]

Can any one apply this to the workbook attached, as i having difficulties with the formula

[estige]

another possibillity:
=SUMPRODUCT((data!A$2:A$1480<>"")/COUNTIF(data!A$2:A$1480,data!A$2:A$1480&""))

[estige]

PS Maybe your problem with Haseeb A's solution is because of an unwanted space (typo I'm sure). Try deleting the space from "A14 80".

[masond3]

Hi Estige

thank you for your help

i have used your formula in data, and its saying that there is
17corps
149 chain
1276 – external id ( i checked corpsid, manually and it worked out correct,so i assume chain and external id is also correct) i

is there any way now, i can determine how many externalid go into the chain ?

so how many 1276externalid are linked to the 149 chains ?
and then how many of those chains, so 149, are linked to those 17 corps ?

[estige]

I'm not sure I understand what you meen, but maybe:
=SUMPRODUCT((data!A$2:A$1480<>"")/COUNTIF(data!A$2:A$1480,data!A$2:A$1480&"")*(data!$B$2:$B$1480<>""))
=SUMPRODUCT((data!A$2:A$1480<>"")/COUNTIF(data!A$2:A$1480,data!A$2:A$1480&"")*(data!$A$2:$A$1480<>""))

The first (chain) returns 376, the second (corp) 43.

[masond3]

can you explain what this formula means, =SUMPRODUCT((data!A$2:A$1480<>"")/COUNTIF(data!A$2:A$1480,data!A$2:A$1480&"")*(data!$B$2:$B$1480<>"")) ?

[estige]

Sorry, I gave you rong gormulaes. It should be:
=SUMPRODUCT((data!D$2:D$1480<>"")/COUNTIF(data!D$2:D$1480,data!D$2:D$1480&"")*(data!$B$2:$B$1480<>""))
=SUMPRODUCT((data!D$2:D$1480<>"")/COUNTIF(data!D$2:D$1480,data!D$2:D$1480&"")*(data!$A$2:$A$1480<>""))
The first one calculates number of unice extarnalid in rows where there is a value in column B (chain)
The second similar for column A Corps

[masond3]

Okay

sorry to be a pain here, i am trying to get my head around the formula . im not sure if the formula wants it do what i want or not ! (i am confused.com lol)

i give you an example of what i am trying to achieve
columnB - Chain id, row 3-8 all have the same chain id, if you move across to column A, row 3-8 all have the same corpid,
I am trying to do a count where it looks at columns A, identifies how many of the id, are duplicates, how many chain ids are linked to that corp. so in this instance it is 7, and how many external id(columnD) are linked to the chainid in column b, so this would also be 7

[estige]

I'm not sure I understand what you mean. I understand what you are saying for that particular chainid, but you want this summed for all chainids, not for each ID?
If you do the same for all chainids and sum the results, won't the result be that what you are asking how many cells with chainid have a corpid next to it?
If this is the case maybe =SUMPRODUCT((data!B$2:B$1480<>"")*(data!A$2:A$1480<>""))

[masond3]

Hi ,

yeah i need it summed for all the ids, that was just an example !
and that formula doesnt seem to be working

[estige]

What dou you mean? It returns 43, and that seems to be right? As far as I can see this is the correct number of rows with value in both column A and B.

[masond3]

HI Please find attached worksheet

hopefully this will explain it better

[estige]

When I summed your examples I got the same results that I got with the formulaes I gave you. Is this not the results you expected (green cells in attachemant)

[masond3]

HI

No this isnt the desired result, the result ideally should be like the one in worksheet , i need to a break down of each indivudal cell, but group them if possible if they have same corp, or chain ?

[estige]

Something like this?