Solved

VBA code to select distinct rows from an Excel Spreadsheet

Posted on 2006-06-26
5
2,712 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

623 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