Sorting by picture (Not sorting with picture)...

[WTW_4]

I found a table on the Internet with some information that is useful to me. The table consists of about 700+ rows of data with seven columns. For each row, five of those columns contains either a checkmark or a dollar sign, or nothing at all.

I copied this table and pasted it into Excel 2007. I am able to sort the table by the data in the two columns that do not contain checkmarks or dollar signs, but would like to be able to sort the table by the columns with the checkmarks and dollars signs such that the rows with the checkmarks are grouped together and the rows with the dollar signs are grouped together.

Unfortunately, the checkmarks and dollar signs that were used in the table are images of checkmarks and dollar signs. When I attempt to sort the table by the five columns with the checkmarks and dollar signs nothing happens. The sort function behaves as though there was nothing at all in the cells even if the cell 'contains' an image of a checkmark or a dollar sign.

Within the "Sort" dialogue box I can select what I want to sort. My choices are:
Values
Cell Color
Font Color
Cell Icon - (I'm not sure what this is, but I tried sorting on it to no effect.)

Too bad there's not an option to sort on some property that is associated with images that are 'attached' to the cell.

I spent some time searching for a solution to this problem and found many articles explaining how to cause images to sort along with data. That seems to be working just fine. I need to sort by the images themselves, or at least by some property that is associated with the images.

This isn't the first time I've encountered this problem as I have often found some table or other on the Internet with interesting data and have had the same problem. Does anyone know of a way to do this?

Thanks much...

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

You'll probably need to use VBA to do that.

If the names of the images are the same for all "$" and the same (or follow a pattern) for all checkmarks, then VBA could be used replace the images with text values that could be used for sorting and filtering.

 

[mikerickson]

If there are only two types of cell entries, checkmark cells vs. dollar sign cells, and A1 is a checkmark cell, the formula =$A$1 might be used to make a helper column for sorting and filtering.

 

[Jerry Sullivan]

Mike, Just to clarify, wouldn't the user still need to use VBA or a manual process to add values distinguishing the two images?

Once that's done (whether the values are added to a helper column or used to replace the images), the user should be able to sort and filter on those values directly.

 

[WTW_4]

If I right-click on an image and select "Size and Properties..." from the shortcut menu that appears, then select the "Alt Text" tab I see an "Alternative text" field. In that field I see an http address for the image file that was used to display the image on the web page. All of the checkmarks were created from a file named "tick_icon.png" and all of the dollar signs were created from a file named "dollor_icon.png" (a misspelling of "dollar"). I'd like to replace each image of a checkmark with a "Y" that is inside the associated cell and each image of a dollar sign with a "$" that is inside the associated cell. Having done this, I would then be able to sort the table based upon the values in these cells.

I'm sure you both already know this, but it seems that the images are associated with the cells, but not part of the cell. If I copy one of the columns with the images and paste it, I get another column with images. If I copy one of the columns with the images and do a "Paste-Special-All" I get a column with no images. So, it seems that any sort of operation that looks at the content of a cell will not work. It seems that the images are attached to, or associated with, the cell, but not part of the cell.

I've never yet used VBA, but would VBA be able to look at the properties of the images even though those images are not inside the cells, but rather attached to the cells? Thanks for your help...

 

[mikerickson]

The OP says that cells contain dollar signs and images.
I'm not sure if that refers to those are cell contents or Shape objects, on the graphics layer, above a cell not in it.

If they are cell contents, then there is a formula approach.
But if they are shapes, I've got a notion for a custom class that would help with sorting.

Edit: I just read the last response. VBA is needed.

 

[WTW_4]

I guess the images of checkmarks and dollar signs are in the "graphics layer" you mentioned. Can VBA see objects that are in the graphics layer - above a cell, but not in it?

 

[Jerry Sullivan]

Here's some code that you can try that will attempt to replace images with text values.

Since the placement of images in relation to the cells can be inexact, the code takes a conservative approach of not overwriting any existing cell values.

Be sure to try this on a copy of your workbook so you can recover easily if there are unexpected results.

Instructions:
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the open workbook in which you want to run this macro
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, with your workbook open, press ALT + F8 to display the Run Macro Dialog.
9. Double Click the macro's name to Run it.

Sub ReplaceShapes()
'--purpose is to replace shape images with cell values for sorting
'  or other processing on the data layer of the worksheet.

'--this procedure looks at all shapes in activesheet.
'  if a shape matches criteria and there is not an existing value
'  in the shape's topLeftCell, a specified value is placed in the topleftcell.
'  optionally, the shape can be deleted or retained.

 Dim shp As Shape
 Dim sAltText As String, sChar As String
 
 For Each shp In ActiveSheet.Shapes
   sChar = vbNullString
   sAltText = LCase$(shp.AlternativeText)
   Select Case True
      Case InStr(1, sAltText, "tick_icon.png") > 0
         sChar = "Y"
      Case InStr(1, sAltText, "dollor_icon.png") > 0
         sChar = "$"
      Case Else
         '--do nothing
   End Select
   
   If Len(sChar) Then
      With shp.TopLeftCell
         If Len(.Value) Then
            MsgBox "Existing value will not be replaced in cell: " _
               & .Address
         Else
            .Value = sChar
            '--optional: delete shape
            shp.Delete
         End If
      End With
   End If
 Next shp
End Sub

 

[mikerickson]

You could also set each shape to MoveButDon'tSize with cells and the image will move when the range is sorted

shp.Placement = xlMove

 

[Jerry Sullivan]

You could also set each shape to MoveButDon'tSize with cells and the image will move when the range is sorted

shp.Placement = xlMove

Edit: I misread. Yes that would be good to add for all shapes.