Solved

VBA code to select distinct rows from an Excel Spreadsheet

Posted on 2006-06-26
5
2,679 Views
Last Modified: 2012-05-05
I have a spreadsheet that will have a column that contains either True or False.  Another column contain the Job Number.

I'm attempting to write a function(I think that's the best approach) that will search and find the first "False" line item for each Job Number.

Each Job Number will have 5 - 10 rows in the spreadsheet and I'm currently not doing any grouping or subset of rows for each job number so I'm really looking at fairly raw data and would like to be able to use only the first line with a False value for each job in a seperate calculation I'm doing.
 
Any thoughts or sample code would be much appreciated.

Thanks,
Jon
0
Comment
Question by:JMO9966
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 16989855
why don't u use the advanced filters??
0
 
LVL 8

Accepted Solution

by:
hiteshgupta1 earned 250 total points
ID: 16989884
Click on Data -> Filter -> Advanced Filter. Then click the "Unique Values" check box and click OK
Remember,if u want to have unique values on the basis of a particular column only den select the range accordingly

               
0
 

Author Comment

by:JMO9966
ID: 16992125
Thanks, but there's not a good way to select distinct. I want all the rows on the spreadsheet, but only want to use the first occurence the third columns value being false in a formula in the 4th column Here's a  simple layout of the three columns.  If I use your idea, it will drop records off the spreadsheet which I don't want.

1)Job Number        2)Seqeuence (each job's route sheet will start with 1,2,3,4,etc...)         3)Inside_OP (0 = True, 1=False)        4)Formula


Each job may have more than one line where Inside_OP is set to False, I want all records to show on the spreadsheet, but I'm looking for a way to find the first occurence of Inside_Op for each job and use the row for my calculation in the 4th column.  The remaining lines with Inside_Op set to False would be set to zero in the calculation so we don't "double-dip".

Thanks again,
Jon
0
 

Author Comment

by:JMO9966
ID: 16992177
If I use your idea and was willing to drop records off the spreadsheet, you can see with my columns this wouldn't work.  If I go unique based off of Job Number and Inside_Op I'm assuming it will show only two lines for each job, one line for True and one record for False if it exists.  I want all records with a value of True to show and use the first row for each job Number where Inside_Op is false in a calculation.  This spreadsheet will have multiple job numbers all within one sheet, I fed this with a query that does group by job number.

Thanks,
Jon
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

19 Experts available now in Live!

Get 1:1 Help Now