Link to home
Start Free TrialLog in
Avatar of JMO9966
JMO9966

asked on

VBA code to select distinct rows from an Excel Spreadsheet

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
Avatar of hiteshgupta1
hiteshgupta1

why don't u use the advanced filters??
ASKER CERTIFIED SOLUTION
Avatar of hiteshgupta1
hiteshgupta1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JMO9966

ASKER

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
Avatar of JMO9966

ASKER

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