Solved

VBA code to select distinct rows from an Excel Spreadsheet

Posted on 2006-06-26
5
2,706 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using "ScreenUpdating" 6 86
Excel VBA, find a string in a column, update a cell 7 117
vbModal 12 74
pop out of webbrowser1 control vba6 5 51
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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

734 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