Organizing data

[Closed]
Report
Posts
2
Registration date
Saturday November 10, 2012
Status
Member
Last seen
January 29, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

This is not as clear as it could be but I will try. I have a spreadsheet that has two columns. The left column (A) is populated by 8-Digit houseID's. The second column (B) is populated by vehicle types. The vehicle is a number from 1 to 7. If a household has more than one vehicle, there will be more than one row entry for that houseid. There is an example below.

What I am wanting to do is construct a new variable based on two criteria. One, that the houseID is the same. Two, if the vehicle type is the same or not. In this case, the variable
will be called SUB. It will take a value of 1 if the houseID is the same but not the type. If the houseid is the same and the vehicle types are all the same then it will take a value of 0. This tells someone if they have multiple vehicle types. Now, I do not think that this is possible but I would like there only to be one SUB value for a set of identical houseID's. For the example below, there would be two SUB values because there are only two houseIDs

HouseID Type SUB
10110000 1
10110000 2 1
10120000 2 0

thanks for any help that anyone can offer.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
try some thing like this

=IF(COUNTIF(A:A,A2)=1,1,IF(COUNTIF(A:A,A2)<>SUMPRODUCT((A:A=A2)*(B:B=B2)*1),0,""))
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month