Condensing Data in Excel Spreadsheet

[theplague187]

Hi,

I have a large amount of data that I need to condense in order to minimise file size for emailing.

I need to condense data that looks something like this:
[TABLE="width: 486"]
<col style="width: 61pt;" width="81" span="6"> <tbody>[TR]
[TD="class: xl66, width: 81, align: center"]Surname
[/TD]
[TD="class: xl66, width: 81, align: center"]Address
[/TD]
[TD="class: xl66, width: 81, align: center"]Suburb
[/TD]
[TD="class: xl66, width: 81, align: center"]City
[/TD]
[TD="class: xl66, width: 81, align: center"]Salary
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Smith[/TD]
[TD="class: xl65, align: center"]12 1st Avenue[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]13500
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Smith[/TD]
[TD="class: xl65, align: center"]12 1st Avenue[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]35000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Smith[/TD]
[TD="class: xl65, align: center"]12 1st Avenue[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]6000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Arnold[/TD]
[TD="class: xl65, align: center"]56 4th Street[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]15000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Arnold[/TD]
[TD="class: xl65, align: center"]56 4th Street[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]18000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Jones[/TD]
[TD="class: xl65, align: center"]198 12th Street[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]22000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Jones[/TD]
[TD="class: xl65, align: center"]198 12th Street[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]9000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Jones[/TD]
[TD="class: xl65, align: center"]67 4th Avenue[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]13000[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Into something like this:
[TABLE="width: 405"]
<col style="width: 61pt;" width="81" span="5"> <tbody>[TR]
[TD="class: xl66, width: 81, align: center"]Surname
[/TD]
[TD="class: xl66, width: 81, align: center"]Address[/TD]
[TD="class: xl66, width: 81, align: center"]Suburb[/TD]
[TD="class: xl66, width: 81, align: center"]City[/TD]
[TD="class: xl66, width: 81, align: center"]Salary[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Smith[/TD]
[TD="class: xl65, align: center"]12 1st Avenue[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]54500[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Arnold[/TD]
[TD="class: xl65, align: center"]56 4th Street[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]33000
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Jones[/TD]
[TD="class: xl65, align: center"]198 12th Street[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]31000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Jones[/TD]
[TD="class: xl65, align: center"]67 4th Avenue[/TD]
[TD="class: xl65, align: center"]Eden[/TD]
[TD="class: xl65, align: center"]Hell[/TD]
[TD="class: xl65, align: center"]13000[/TD]
[/TR]
</tbody>[/TABLE]


The data I'm working with has a lot more fields than this example, so a pivot table is not an option as the final data needs to be in the same format as the current data. There are also some blank entries in some fields for certain records which are not in error.

Any assistance would be greatly appreciated.

Thanks.

 

[AmNimru]

Hello,

Personally, I would just make a copy of the sheet (let's name it "Copy") and then:
1. Created unique entries by combining Surname, Address, Suburb, City in both sheets (If, for instance, "Smith" is in A2, then I would put the formula in F2: =A2&B2&C2&D2)
2. On the sheet "Copy", use the built-in function of removing duplicates, based on the formula in F column
3. Use =SUMIF function for Salary on the "Copy" sheet with data from the original sheet, based on column F search criteria

I believe that would pretty much give you what you want.

Hth

 

[theplague187]

Thanks for the suggestion, giving it a try now.

One potential problem I'm anticipating is that there are multiple fields that will need to be summed for duplicates, will I simply need to use the =SUMIF function for each of those fields?

 

[AmNimru]

Yes, that would be pretty much the general idea.

 

[theplague187]

Ok I've completed step 1 of your suggestion, now I need to sum all duplicates for columns B through G.

I can't get the =SUMIF function to work for unspecified entries in column A - i.e. the function requires me to specify the entry in column A.

Now I need to get the data, which now looks like this:
[TABLE="width: 463"]
<col style="width: 50pt;" width="67"> <col style="width: 50pt;" width="66" span="2"> <col style="width: 50pt;" width="67"> <col style="width: 49pt;" width="65" span="2"> <col style="width: 50pt;" width="67"> <tbody>[TR]
[TD="width: 67"]Column A
[/TD]
[TD="width: 66"]Column B[/TD]
[TD="width: 66"]Column C[/TD]
[TD="width: 67"]Column D[/TD]
[TD="width: 65"]Column E[/TD]
[TD="width: 65"]Column F[/TD]
[TD="width: 67"]Column G
[/TD]
[/TR]
[TR]
[TD]Identifier
[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaab[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaab[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaab[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaad[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]aaad[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</tbody> [/TABLE]


to look like this:
[TABLE="width: 463"]
<col style="width: 50pt;" width="67"> <col style="width: 50pt;" width="66" span="2"> <col style="width: 50pt;" width="67"> <col style="width: 49pt;" width="65" span="2"> <col style="width: 50pt;" width="67"> <tbody>[TR]
[TD="width: 67"]Column A
[/TD]
[TD="width: 66"]Column B[/TD]
[TD="width: 66"]Column C[/TD]
[TD="width: 67"]Column D[/TD]
[TD="width: 65"]Column E[/TD]
[TD="width: 65"]Column F[/TD]
[TD="width: 67"]Column G
[/TD]
[/TR]
[TR]
[TD]Identifier
[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]aaab[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]aaac[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]aaad[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody> [/TABLE]


I can see the light at the end of the tunnel now.

 

[AmNimru]

Yes, and to get the unique Identifier, you only need to copy all of them somewhere and remove duplicates (that's why I said to make a copy of the worksheet, it's the easiest).
Let me know if it works the way you imagined it.