Searching through Excel Rows

James0903
James0903 used Ask the Experts™
on
Sir/Madam,
In the attached spreadsheet, I have rows of data.  Due to the input from our customer, all data appears on one row.  My goal is:

- Macro created (I have not written them before) that reviews each row.  If the value of "JJ" appears, followed by an airport listed in the 'Master Airports' tab, copy the row to a new tab in the spreadsheet. Example (row #5 in my spreadsheet):  
US,2012,1,00336,000001,ABE,JJ,UA,X,CLT,16,UA,X,ABE
"JJ" appears, and an airport listed in the 'Master Airport' list, that of 'CLT' will result in this row being copied to a new tab.  The amount of spaces between "JJ" and "CLT" will always be 7 (data of ",UA,X,").  The row of data may extend out but the distance will always be 7.

Simpler:
If finding the JJ and a corresponding Master Airport is too difficult, then simply, wherever "JJ" appears in the row, copy that row to a tab in the spreadsheet.

Please see attached for example.  The rows highlighted in yellow on the 'RAW' tab are what I want copied to the 'JJ' tab.  

FYI, the data provided is barely 1% of my data....I work with a lot of data!

Thank you as always!
ExpertsExchange-Question6.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Unless it must be a macro, you can use data filter very conveniently.

Data > Filter
from the dropdown at the top of column A select Text filters > Contains...
enter ,jj,
Ok


This will give you a filtered list which you can conveniently copy to the other sheet.

Author

Commented:
ssaqibh, thank you.  I can perform without a macro, but what about the comparison against the master airport list?  Will that be too difficult to do?  Showing JJ is great but I want to show only those JJ's in which the airport listed 7 places after the JJ (anywhere on the line) is on the Master Airport list.  This will alleviate me wedding through everything. Thank you!
Enter this formula in B1 and copy it all the way down. You can then filter on this column.

=AND(MID(A1,28,2)="JJ",NOT(ISNA(MATCH(MID(A1,36,3),'MASTER AIRPORTS'!A:A,0))))


BTW I like this:
This will alleviate me wedding through everything
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Author

Commented:
ssaqibh, thank you for this proposed solution, but, copying this formula down 150,000 rows of data then trying to filter out what I want can be quick tedious.  Is there any other way?  

Thanks!


Funny....my wedding anniversary is coming up and I am trying to figure out what to get!
Try this macro

Sub filterNcopy()
    Range("B1:B" & Cells.SpecialCells(xlCellTypeLastCell).Row).FormulaR1C1 = _
        "=if(AND(MID(RC[-1],28,2)=""JJ"",NOT(ISNA(MATCH(MID(RC[-1],36,3),'MASTER AIRPORTS'!C[-1],0)))),"""",false)"
    Range("B1:B" & Cells.SpecialCells(xlCellTypeLastCell).Row).Value = Range("B1:B" & Cells.SpecialCells(xlCellTypeLastCell).Row).Value
    Range("B1:B" & Cells.SpecialCells(xlCellTypeLastCell).Row).SpecialCells(xlCellTypeBlanks).Offset(, -1).Copy Sheets("JJ").Range("A1")
    Range("B1:B" & Cells.SpecialCells(xlCellTypeLastCell).Row).ClearContents
End Sub

Open in new window


and have a happy anniversary.

Author

Commented:
This is great!  I have run the macro and this is much better.  

One more request, is there a way you can add to the macro that if the first airport in spaces 24-26 is on the 'Master Airport' tab, keep the record, else ignore.  

Basically, through this added step, I am trying to remove international airports from my list, leaving me only the domestic airports that appear on the 'Master Airports' list.  If the first airport (spaces 24-26) is domestic, and it contains a JJ with a domestic airport, copy the record to the JJ tab.  Else, ignore.

Thank you for the anniversary congrats.

Author

Commented:
Another question, the code is only picking up JJ in space 28 and the compare code in space 36.  JJ could appear in the rest of the record as well:  JJ: 40,52,64,76,88,94,106,118,130,142,154,166,178,190 and 202.  The airport code continues to be 8 spaces after: 48,60,72,84,90,102,114,126,138,150,162,174,186,198 and 210.  Can the macro be updated with these additional places to check?  Thank you.

-Or-start one with 40 and 48 and I can do the rest.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial