Avatar of James0903
James0903
 asked on

Searching through Excel Rows

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
Microsoft Excel

Avatar of undefined
Last Comment
James0903

8/22/2022 - Mon
Saqib Husain

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.
James0903

ASKER
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!
Saqib Husain

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
James0903

ASKER
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!
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
James0903

ASKER
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.
James0903

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.