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:

Surname	Address	Suburb	City	Salary
Smith	12 1st Avenue	Eden	Hell	13500
Smith	12 1st Avenue	Eden	Hell	35000
Smith	12 1st Avenue	Eden	Hell	6000
Arnold	56 4th Street	Eden	Hell	15000
Arnold	56 4th Street	Eden	Hell	18000
Jones	198 12th Street	Eden	Hell	22000
Jones	198 12th Street	Eden	Hell	9000
Jones	67 4th Avenue	Eden	Hell	13000

<col style="width: 61pt;" width="81" span="6"> <tbody>
</tbody>

Into something like this:

Surname	Address	Suburb	City	Salary
Smith	12 1st Avenue	Eden	Hell	54500
Arnold	56 4th Street	Eden	Hell	33000
Jones	198 12th Street	Eden	Hell	31000
Jones	67 4th Avenue	Eden	Hell	13000

<col style="width: 61pt;" width="81" span="5"> <tbody>
</tbody>

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:

Column A	Column B	Column C	Column D	Column E	Column F	Column G
Identifier	Number	Number	Number	Number	Number	Number
aaaa	4	4	4	4	4	4
aaaa	4	4	4	4	4	4
aaaa	4	4	4	4	4	4
aaaa	4	4	4	4	4	4
aaab	4	4	4	4	4	4
aaab	4	4	4	4	4	4
aaab	4	4	4	4	4	4
aaac	4	4	4	4	4	4
aaac	4	4	4	4	4	4
aaac	4	4	4	4	4	4
aaac	4	4	4	4	4	4
aaac	4	4	4	4	4	4
aaac	4	4	4	4	4	4
aaad	4	4	4	4	4	4
aaad	4	4	4	4	4	4

<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>
</tbody>

to look like this:

Column A	Column B	Column C	Column D	Column E	Column F	Column G
Identifier	Number	Number	Number	Number	Number	Number
aaaa	16	16	16	16	16	16
aaab	12	12	12	12	12	12
aaac	20	20	20	20	20	20
aaad	8	8	8	8	8	8

<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>
</tbody>

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.