Complicated removal of duplicates in excel
I have a complicated situation here on removing duplicates.
Let me explain as detailed as possible here.
Attached is a file I'm attempting to submit.
Column A is a census tract number
Column B either has a 1 or a 0 indicating a service type
Column C has number of circuits
Column D has number of circuits that fit a specific description.
When I submit this file it looks as though there is an issue with duplicate census tract numbers...which is very possible considering one "customer" at the same census tract can have multiple services and multiple circuits at each respectively.
What I need is a method of combining data to remove duplicates in column A.
My results need to be as follows: (I will provide examples as best i can to help you follow my line of thinking)
1) I need to separate this file into 2 separate files based off of Column B - all of the 0's in one file/sheet and all of the 1's in another. (this is easy enough to do, but i did not move anything so you could see the entire issue at hand)
2) For all census tract numbers in Column A that also match in Column B, I need the number of circuits combined. For example Cell A104 has the same census tract as Cell A 105, and they both of 0 in Column B. These would need combined and then their value in Column C would need added together. So it would look like this 21193971000,0,2,0
3) Once their circuit values or Column C values have been combined all duplicate census tracts would then need removed so there is only one census tract.
4) The same would need done to all items that have a Column B value of 1.
Can you help please?
2014-U4-477-filing.csv
Let me explain as detailed as possible here.
Attached is a file I'm attempting to submit.
Column A is a census tract number
Column B either has a 1 or a 0 indicating a service type
Column C has number of circuits
Column D has number of circuits that fit a specific description.
When I submit this file it looks as though there is an issue with duplicate census tract numbers...which is very possible considering one "customer" at the same census tract can have multiple services and multiple circuits at each respectively.
What I need is a method of combining data to remove duplicates in column A.
My results need to be as follows: (I will provide examples as best i can to help you follow my line of thinking)
1) I need to separate this file into 2 separate files based off of Column B - all of the 0's in one file/sheet and all of the 1's in another. (this is easy enough to do, but i did not move anything so you could see the entire issue at hand)
2) For all census tract numbers in Column A that also match in Column B, I need the number of circuits combined. For example Cell A104 has the same census tract as Cell A 105, and they both of 0 in Column B. These would need combined and then their value in Column C would need added together. So it would look like this 21193971000,0,2,0
3) Once their circuit values or Column C values have been combined all duplicate census tracts would then need removed so there is only one census tract.
4) The same would need done to all items that have a Column B value of 1.
Can you help please?
2014-U4-477-filing.csv
aikimark
what about column D?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That will give me what I need. Thank you very much.