Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




6119 posts

Uber Geek
+1 received by user: 988

Trusted
Lifetime subscriber

Topic # 187926 10-Dec-2015 08:47
Send private message

Morning all,

I have an Excel spreadsheet containing data on physical sites (about 4000 rows), each with a unique ID. I also have a second table (in the same file) from our GIS system which lists the IDs of sites within a certain geographic area (this has about 1000 rows).

What I want to do is filter the main table so that it only shows the 1000 rows that I'm interested in. I've played around with the advanced filters but can't figure out how to get this working. I've also tried conditional formatting with no luck.

How can I accomplish this? Are any more details required?

Thanks :)

Create new topic

k14

578 posts

Ultimate Geek
+1 received by user: 75


  Reply # 1446939 10-Dec-2015 09:30
One person supports this post
Send private message

Might need a bit more info but from what you describe it sounds like using a pivot table might be the best option. Look on youtube for some tutorials on how to use them.

What does this tag do
933 posts

Ultimate Geek
+1 received by user: 191

Subscriber

  Reply # 1446944 10-Dec-2015 09:34
Send private message

Yeah would recommend a pivot table, are you wanting to edit the data or is this for viewing it?
If for viewing you could try out something like Qlik Sense Desktop (free) and explore data with that.

Actually k14 if OP does want to edit, can you edit data via a Pivot Table or is it just read only?
If needing to edit may just have to use the row header filters..

 
 
 
 


Try Wrike: fast, easy, and efficient project collaboration software


6119 posts

Uber Geek
+1 received by user: 988

Trusted
Lifetime subscriber

  Reply # 1446946 10-Dec-2015 09:34
Send private message

Aha! The thought did cross my mind but I didn't know how they worked and didn't want to waste time learning them if it wasn't the right tool. Thanks for the pointer; it looks like some reading is in order :)

282 posts

Ultimate Geek
+1 received by user: 76


  Reply # 1446950 10-Dec-2015 09:41
2 people support this post
Send private message

An alternative way to do this would be vlookups. While a pivot table is probably the right tool for this instance, they are handy to know about so you could go and look into those as well if you are so inclined.

1234 posts

Uber Geek
+1 received by user: 522


  Reply # 1447014 10-Dec-2015 10:49
Send private message

I don't know if I'll explain this very well, but...

A basic way to do it would be to add a column to the 4000 row table with a formula in it something like =IF(COUNTIF(xx$a:xx$b,yya)<>0,1,0) where xx$a:xx$b is the column range in the 1000 row table that contains the site IDS and yya is the cell in the current row of the 4000 row table that has the site ID.  This will put a 1 in the cell if the site ID on that row is in the 1000 row table and a 0 if it's not. You can then filter that column to display only rows that contain a 1.

1494 posts

Uber Geek
+1 received by user: 330


  Reply # 1447023 10-Dec-2015 11:09
Send private message

+1 for andrew027's suggestion because Filtering only works with one range of data. A separate range of data, whether in the same or a different worksheet. requires a formula to create a "join" between the two sets of data.

The one change I would make is to use the vlookup function to return the value you are matching with. That way you can confirm that the ID of the record matches the ID returned by vlookup. You can do this visually by returning the result of the vlookup or you could use an if function to compare the returned value with the ID of the current record/row.

947 posts

Ultimate Geek
+1 received by user: 222

Trusted
Subscriber

  Reply # 1447046 10-Dec-2015 11:38
Send private message

I tend to use a combination of INDEX and MATCH functions.  Bit more complex but I find the results more reliable and powerful than a vLookup.  Here is an email I sent to a colleague on their use;
Description
=INDEX (Column I want a return value from in the other worksheet,(MATCH(My Lookup Value on the current row,Column I want to Lookup against on the other worksheet,0 ),1) Example
=INDEX('Sheet2'!B:B,MATCH('Sheet1'!A2,'Sheet2'!A:A,0),1)

Create a new column on the main worksheet and perform the above on the GIS worksheet.  You will end up with a column that either has a value or '#N/A' where you can then filter out the rows with '#N/A'.

PM me the file if you like or send a subset of obfuscated data if it's confidential - two lines on the main worksheet and one on the GIS worksheet would be enough to show you how it works.




Procrastination eventually pays off.



6119 posts

Uber Geek
+1 received by user: 988

Trusted
Lifetime subscriber

  Reply # 1447051 10-Dec-2015 11:47
Send private message

Whoa, I do some work for a while and get back to a wall of text :P

No further responses required: On the boss' instruction I told someone else to make a PivotTable and all sorted :)

Thanks for the suggestions though!

2251 posts

Uber Geek
+1 received by user: 1073


  Reply # 1447057 10-Dec-2015 11:50
Send private message

Do it the other way round from Andrew's suggestion.

Start with the 1000-row table and columns to use VLOOKUP() to extract the data you want from the 4000-row table.


Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

TCF and Telcos Toughen Up on Scam Callers
Posted 23-Apr-2018 09:39


Amazon launches the International Shopping Experience in the Amazon Shopping App
Posted 19-Apr-2018 08:38


Spark New Zealand and TVNZ to bring coverage of Rugby World Cup 2019
Posted 16-Apr-2018 06:55


How Google can seize Microsoft Office crown
Posted 14-Apr-2018 11:08


How back office transformation drives IRD efficiency
Posted 12-Apr-2018 21:15


iPod laws in a smartphone world: will we ever get copyright right?
Posted 12-Apr-2018 21:13


Lightbox service using big data and analytics to learn more about customers
Posted 9-Apr-2018 12:11


111 mobile caller location extended to iOS
Posted 6-Apr-2018 13:50


Huawei announces the HUAWEI P20 series
Posted 29-Mar-2018 11:41


Symantec Internet Security Threat Report shows increased endpoint technology risks
Posted 26-Mar-2018 18:29


Spark switches on long-range IoT network across New Zealand
Posted 26-Mar-2018 18:22


Stuff Pix enters streaming video market
Posted 21-Mar-2018 09:18


Windows no longer Microsoft’s main focus
Posted 13-Mar-2018 07:47


Why phone makers are obsessed with cameras
Posted 11-Mar-2018 12:25


New Zealand Adopts International Open Data Charter
Posted 3-Mar-2018 12:48



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.