Solved

De-dupe and count/calculation in Excel

Posted on 2014-11-18
24
131 Views
Last Modified: 2014-11-27
The file has a list of addresses and each address may appear several times with different dates. The address row for each property also includes number of bedrooms. Most properties have between 1 and 6 bedrooms.
The objective is to ignore the dates, de-dupe the list then count the number of properties by number of bedrooms. Then calculate the total number of bedrooms.
The example file contains examples of the three lists.
Suburbs--Properties-Bedrooms2014.xlsx
0
Comment
Question by:gregfthompson
  • 14
  • 10
24 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40450191
I haven't looked at the file but I suspect the way forward might be a pivot table.

Address as a row header, number of bedrooms as value. Assuming all entries for each address are the same, you can set the number of bedrooms as an average or max or min rather than a sum.

Thanks
Rob H
0
 

Author Comment

by:gregfthompson
ID: 40453729
Hi Rob,

Thanks for your comment.
The existing list is a property addresses  with number of bedrooms and a month and year. Some properties area listed under several different months.
The de-duped list removes the month and year and sorts the properties into suburb by number of bedrooms. Unique properties only.
The final list counts the number of properties under for each number of bedrooms and calculates the total number of bedrooms for each suburb.
In this example, Abbotsford has  96 properties with 1 bedroom, 167 with 2 bedrooms, 25 with 3 bedrooms, 1 with 4 bedrooms, 0 with 5 bedrooms, 1 with 6 bedrooms and 0 with 7 and 8 bedrooms. The total number of bedrooms is 1x96 + 2x167 + 3x25 + 4x1 + 6x1 = 849

Does this make sense?:
Can this be done with a macro?

Thanks,

Greg
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40453758
Can you provide some sample data?
0
 

Author Comment

by:gregfthompson
ID: 40453845
Hi Rob,

Thanks for your response.

The sample data is in columns A to J in the attached file.  The de-duped list is in columns L to O and the output sample is in columns Q to AB.

I'm not sure what other data you are seeking.

Thanks,

Greg
0
 

Author Comment

by:gregfthompson
ID: 40453857
PS I've attached the file again
Suburbs--Properties-Bedrooms2014.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40454869
Greg,

See attached.

Sheet2 - I have created a Pivot Table on the original list to pull out a list of Suburbs and their Post Code.

Sheet1 - Columns Q to AB; copied Suburb and Post Code from Pivot into column Q & R then used COUNTIFS formula to populate count of properties by Suburb and by number of bedrooms. Total column then uses SUMPRODUCT formula to multiply number of properties by number of bedrooms.

I haven't used the de-duped list as I have been able to drive it all from original list.

Hope this is what you need.

Thanks,
Rob H
Copy-of-Suburbs-Properties-Bedrooms2014.
0
 

Author Comment

by:gregfthompson
ID: 40456175
Thanks Rob,

I have never seen or used Pivot tables and after I unzipped this download I could not work out how to find the worksheets.

I've had at look at Microsoft help on Pivot Tables and I'm still unable to work out how to open them.

Is there a better tutorial you can recommend?  I have about a 160,000 rows that I want to sort.

Thanks,

Greg
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40456354
The upload should not have needed unzipping. I uploaded an excel file with two sheets.
0
 

Author Comment

by:gregfthompson
ID: 40456487
Thanks Rob.

The file named Copy-of-Suburbs-Bedrooms2014 downloads as a zipped file. Lots of xml - no xlsx sheets anywhere that I can find.

Can you upload it again?

Thanks,

Greg
PS I've attached the file that downloads
Copy-of-Suburbs-Properties-Bedrooms2014-
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40457025
Ok, I have just been able to download it correctly.

When I click on the file link in the message, I see the usual download window with two options: Open With or Save.

As the file isn't recognised, the Save option is checked. I selected the Open With option and clicked the Browse button to choose Excel. I got an error message saying that file was in different format but clicked Yes to open anyway. The file opened fine in Excel and can see the two sheets as expected. Click on the Enable Editing button at the top to get full functionality.

If this looks like want you want, I will walk you through creating the Pivot table. There are a couple of more advanced options that you might want to use as well.

Thanks
Rob H
0
 

Author Comment

by:gregfthompson
ID: 40461806
Thanks Rob,

It's now open.  I'll have a good look at it and see if I can gain more understanding.

I'll try to get back to you tomorrow.

Thanks again,

Greg
0
 

Author Comment

by:gregfthompson
ID: 40461809
PS Apologies for not responding more quickly.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 40461883
No worries, hope it does what you want.
0
 

Author Comment

by:gregfthompson
ID: 40463359
Hi Rob,

I have no idea how you did this but I think I can use it by simply copying in the total list on sheet 2.

The only missing data is the count for number of properties by bedrooms. I'm seeking to know how many properties for 1 bedroom, how many properties for 2 bedroom and same for 3 bedroom, 4 bedroom, 5 bedroom, 6 bedroom 7 bedroom and 8 bedroom.

Can the pivot table show these totals in sheet 1?

Thanks again,

Greg
0
 

Author Comment

by:gregfthompson
ID: 40463370
PS It is very elegant - love the way it works.
0
 

Author Comment

by:gregfthompson
ID: 40463376
PPS. Oops, error.  Where I mention sheet 1, I mean sheet 2 and where I mention sheet 2, I mean sheet 1.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40463482
The table of COUNTIFS formulas shows the count by number of bedrooms. Not sure what you're missing.
0
 

Author Comment

by:gregfthompson
ID: 40463574
I know this must sound silly but I can't find COUNTIFS. I have found the COUNT and it then changes to Count of Bed,

I'm sorry. I've had a good look at the Pivot Table tutes but still do not understand how it works.
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40464354
In the file I uploaded, columns S to AA were populated with COUNTIFS formula:

=COUNTIFS($B$3:$B$1328,$Q4,$D$3:$D$1328,S$3)

$B$3:$B$1328 refers to column in full list for Suburb and $Q4 refers to Suburb for which count is required, column at side of Summary table;
$D$3:$D$1328 refers to column in full list for number of bedrooms and S$3 refers to number of bedrooms, row at top of summary table.

A new version of file is attached; I wonder if something happened with upload from me, download to you, re-upload by you, download by you....

When I went to save the file I also got a compatibility error message, so made sure I saved with full compatibility. What version of Excel are you using? If using prior to 2007, the COUNTIFS won't work because that function only came in with 2007.

The file now has 3 tabs with appropriate names to avoid confusion.

Thanks,
Rob
Suburbs-Properties-Bedrooms2014.xlsx
0
 

Author Closing Comment

by:gregfthompson
ID: 40465445
Thanks Rob. Beautiful work.

PS. I'm using Excel 2010.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40465662
Glad we got there in the end. If you want some basic tips for pivot tables, let us know.

Thanks
Rob
0
 

Author Comment

by:gregfthompson
ID: 40466021
Hi rob,

Thanks.

I copied about 160,000 rows into the data section.  But I don't know how to get it recognised so that it will work.
I am lost at the moment.

Where isthe pivot table data cell specification?

Thanks,

Greg
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40466373
You will have to update the pivot to include the new data.

Go to pivot tab and put cursor anywhere in table. Blue borders will appear around the table and a couple of extra items will appear; a pane on right hand side listing the available columns from the data and a quad pane where you drag the column fields for displaying in the table. There will also be a new group on the ribbon tool bar. In this group select the options tab and immediately below there is an option to change data source. Click this and a range browser window will appear showing existing data range. If you know the row number you can edit the range manually or use keys or mouse to select the range. Click Ok and the pivot will update. You can then copy and paste the suburb and post code from refreshed pivot into summary sheet. Copy down the formulas to the extent of your new list.

If the data gets updated regularly this can be automated so that the data range used in the pivot updates and you only need to refresh the table and do the copy & paste.

Thanks
Rob
0
 

Author Comment

by:gregfthompson
ID: 40468601
Thanks Rob.

All perfect. Thanks again so much!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
highlight duplicate entry 16 27
Excel Formula 4 28
Excel 2016 - Black cell borders 11 26
Error in VBA for exact date format as in source file 9 16
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now