Link to home
Start Free TrialLog in
Avatar of James0903

asked on

Searching through Excel Rows

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):  
"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.

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!
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

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,

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


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.


BTW I like this:
This will alleviate me wedding through everything
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! wedding anniversary is coming up and I am trying to figure out what to get!
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.