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" 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
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" 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
Last Comment
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!
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
BTW I like this:
=AND(MID(A1,28,2)="JJ",NOT
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
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!
Thanks!
Funny....my wedding anniversary is coming up and I am trying to figure out what to get!
ASKER CERTIFIED SOLUTION
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
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 questionASKER
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.
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.
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,
-Or-start one with 40 and 48 and I can do the rest.
-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.
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.