This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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
Do more with
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.
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.
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
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!
Try this macro
and have a happy anniversary.
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
and have a happy anniversary.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial