?
Solved

Excel macro to filter rows based on case sensitivity & font color

Posted on 2014-08-21
16
Medium Priority
?
612 Views
Last Modified: 2014-08-27
I need to create a macro that filters a large spreadsheet for all rows that meet all of the following criteria:
Red font color only (exclude rows that mixed font colors, ie., red and black)
First word in string is in all capital letters
Row ends with a minimum of 5 spaces preceding a 4-character string in all capital letters that starts with "C"

Thanks!
Andrea
0
Comment
Question by:Andreamary
  • 8
  • 8
16 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40276931
Example data, please, or provide more-specific information on size of data table (ex., number of columns, examples of data that would pass the filter criteria).  

Also:  I presume in the second criteria you mean the first word in the first cell must be all caps.  If different, please clarify.
Same for third criteria; is this the last cell in the row?

Regards,
-Glenn
0
 

Author Comment

by:Andreamary
ID: 40277358
Hi Glenn,

I created a sample workbook. The first tab is sample data, and the second tab shows the results I want to extract from the data. If it's possible to include having the desired results copied into a blank worksheet as part of the solution to my question, that would be great!

Thanks,
Andrea
EE-SampleData-Results.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40277556
The following workbook will add a sheet called "Results" and copy the values that meet your criteria from the source sheet to it.  The macro name is "Move_Select_Data".

Notes:
1) You must be viewing the source sheet to run.
2) It is looking for a color IDENTICAL to that given in your example (RGB values 238,46,41).  If it is any other variation of red - including pure red (255,0,0), it will not pick that result as a possible match.

The results are not formatted; just the values are added to the Results sheet.  You can run this over again and the code will replace any existing Results with new ones.

Regards,
-Glenn
EE-SampleData-Results.xlsm
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Andreamary
ID: 40278991
Hi Glenn,

Thanks so much!!

Unfortunately there is a weird thing that is happening, which is to do with my data since the sample data I sent is not a problem, and I'm not sure what is causing it:

When I ran the macro, it created the Results sheet but there were no results listed. Thinking that maybe the red font color in the values wasn't identical to the RGB values in the macro, I checked the first value and the RGB values were correct. When I ran the macro again, the single value that I double-clicked on was successfully transferred to the Results spreadsheet. I continued to experiment, and for every value I double-clicked on, that value was transferred across when re-running the macro. Any value I didn't double-click on was not transferred. So somehow the RGB values are not recognized until that row is double-clicked.

The reason my sample data spreadsheet didn't cause any issues is that I had to edit each row (for confidentiality reasons).

Background on the data, in case it sheds some light:
The original source of this data is a PDF that was opened in Acrobat Pro and saved as a Microsoft Excel spreadsheet.

Any thoughts on this strangeness? Or perhaps the best bet is to adjust the macro so it emulates the double-clicking action I am doing for all rows before it runs through the main event?

Thanks,
Andrea
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40279504
I have a macro that I run that "refreshes" large, selected ranges of data.  It essentially does something like you did - double-clicking and re-entering each cell (like [F2] and [Enter]).

Add this macro to your workbook (or better yet, your Personal Macro Workbook).  Then select all the data to update and run this macro.  THEN, run my previously-submitted macro.  Let me know if this works.

-Glenn

Sub ReEnter()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim x, tCells As Long
    tCells = Selection.Count
    For x = 1 To tCells
        Selection.Item(x).Formula = Trim(Selection.Item(x).Formula)
    Next x
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:Andreamary
ID: 40279635
Hi Glenn,

Unfortunately running the above macro doesn't seem to make a difference. I tried it a couple of times, and then went back again and manually double-clicked on a row that had one of the values, saved and ran the macro again, and that one value transferred over to the results sheet.

Interestingly, when I copied the spreadsheet into Microsoft OneNote, then re-pasted it back into Excel, the Move_Select_Data then worked. I noticed when the PDF is running through it's conversion into an Excel format, the progress bar at one point refers to OCR, which is why I tried the OneNote process as it has OCR capabilities. I don't know if that has anything to do with the issue here...curious.

Any other thoughts?

Andrea
0
 

Author Closing Comment

by:Andreamary
ID: 40283016
Hi Glenn,

I'm very pleased...thanks so much!

Cheers,
Andrae
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40283298
I'm sorry I didn't get a chance to follow up on your issue with my "Re-Enter" macro.  It only works on a selected range of cells (i.e., you have to highlight/select them all before running the macro), but it still should have refreshed even a single cell like the red ones in your data.

I'm glad I was able to help some.

Regards,
-Glenn
0
 

Author Comment

by:Andreamary
ID: 40283892
Hi Glenn,

Thanks for your follow-up. I ran the "ReEnter" macro after selecting the range of cells as suggested, and unfortunately it removed the font formatting, including the red font so the "Move_Select_Data" macro doesn't work.

If it's possible to get the "ReEnter" macro to run and refresh the data without removing font color, that would be great. Since this thread is closed, should I be posting this as a new question?

Cheers,
Andrea
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40284084
No; since the issue you're having is a result of code I offered here, I think we should try and resolve it within this question.

Is it possible to attach a copy of the file with the PDF data - redacted, if necessary?  I'd like to see the behavior you're talking about and maybe provide another automated solution.

-Glenn
0
 

Author Comment

by:Andreamary
ID: 40285793
Hi Glenn,

Thanks for offering to pursue this.

I've created a sample Excel file as attached that demonstrates the behavior. In the results sheet, you'll notice there are two results — these results are coming from the rows in Table 1 which I double-clicked. The remaining rows that meet the criteria, row 7 and 10, did not transfer over. If I double-click them, however, then save the file and run the macro, they will be included in the results.

I'll be interested to hear if you find the same behavior occurs at your end, and if so, what your take is on this!

Cheers,
Andrea
TestPDFToExcel.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40286346
Very interesting.  I can duplicate your scenario and see that although the cells appear red with an Arial font, the formatting underneath is different (Times New Roman, "Automatic" - black -  font color).

I've created a new updating subroutine ("F2_It") and if your select your data in column A and run this, it will actually execute the [F2] and [Enter] keystrokes on all cells in the range.  I tried to incorporate this code into the original subroutine ("Move_Select_Data") and was not able to get the code to work (still not sure why; the SendKeys command is tempermental in this regard).

So, going forward you need to do the following:
1) upload your PDF data into column A of the sheet
2) Select the data in column A and run "F2_It" macro on it to clean up.
3) Run "Move_Select_Data" macro to move.

The attached workbook has the updated code.

-Glenn
EE-TestPDFToExcel.xlsm
0
 

Author Comment

by:Andreamary
ID: 40288799
Hi Glenn,

Thanks very much for the new macro.

There is a line containing the following text that is causing the macro to hang up:

-H.  ·-

I don't know how often this particular string will recur (the PDF I will be converting  into Excel contains over 1000 pages).

Any thoughts on how best to deal with this in an automated way?

Thanks,
Andrea
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40289063
The reason the macro is hanging up on
-H.  ·-

is because it begins with a hyphen, or as Excel considers it, a minus sign.  As such, Excel is trying to determine the result of the formula and, of course, there is no formula here.  Normally, Excel wouldn't even allow the existence of a cell like this, but since you're getting the data from a PDF file, I guess all bets are off.

I tweaked the F2_It subroutine to change all cells to "Text" format from "General" so that this possibility will be overlooked.  The method is the same as before: Highlight the whole range in column A to update, run F2_It, then run Move_Select_Data.  Updated workbook attached.

-Glenn
EE-TestPDFToExcel.xlsm
0
 

Author Comment

by:Andreamary
ID: 40289481
Terrific, Glenn...works perfectly!! Thank you SO much for all your help!

Best,
Andrea
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40289522
You're Welcome.  This was an interesting challenge.  Just wish I'd been able to combine the two subroutines (still scratching my head on that part).

-Glenn
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question