Sort/filter cells with picture

%3CLINGO-SUB%20id%3D%22lingo-sub-1297272%22%20slang%3D%22en-US%22%3ESort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297272%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20sheet%20some%20cells%20have%20picture%20others%20don't%20is%20there%20a%20quick%20way%20to%20sort%20or%20filter%20the%20items%20which%20have%20pictures%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.JPG%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183532iB27DEF623F226165%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture.JPG%22%20alt%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1297272%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297315%22%20slang%3D%22en-US%22%3ERE%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297315%22%20slang%3D%22en-US%22%3EIs%20the%20image%20literally%20embedded%20in%20the%20cell%3F%20Otherwise%2C%20sorting%20would%20only%20rearrange%20column%20A.%20If%20the%20images%20are%20something%20like%20a%20webding%2Fwingding%2C%20you%20should%20be%20able%20to%20sort%20by%20that.%20And%20if%20you%20wanted%20to%20control%20the%20sort%20order%2C%20you%20could%20use%20column%20C%20to%20provide%20a%20numerical%20value%20to%20sort%20them%20in%20a%20specific%20order%2C%20and%20you%20could%20assign%20the%20%23%20via%20a%20formula%20you%20could%20quickly%20copy%20and%20paste%20down.%20However%2C%20this%20could%20be%20timely%20though%20if%20you%20have%20a%20large%20variety%20of%20images.%20Just%20a%20couple%20ideas.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297332%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297332%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20the%20image%20fits%20within%20the%20cell%2C%20it%20might%20actually%20sort%20with%20the%20row%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616599%22%20target%3D%22_blank%22%3E%40Drin365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20click%20in%20cell%20B2%2C%20go%20to%20the%20DATA%20tab%2C%20and%20select%20the%20easy%20sort%20icon%20for%20Z%20to%20A%20and%20see%20what%20happens.%3C%2FP%3E%3CP%3EYou%20can%20also%20click%20undo%20or%20close%20the%20file%20without%20saving%20if%20the%20results%20are%20not%20as%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20as%20I%20mentioned%2C%20if%20you%20want%20to%20control%20the%20sort%20of%20the%20picture%20you%20may%20need%20to%20do%20some%20kind%20of%20formula%20if%20you%20can.%20Unless%20the%20row%20count%20is%20low%20enough%20for%20you%20to%20eyeball%20it%20(I%20would%20wait%20until%20after%20you%20sort%20so%20the%20similar%20images%20are%20together).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297345%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616575%22%20target%3D%22_blank%22%3E%40HCole718%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFirst%20of%20all%20thanks%20for%20your%20replay.%20Yes%2C%20I%20tried%20with%20the%20filter%20but%20I%20have%20nothing%20to%20sort%20with.%20I%E2%80%99m%20attaching%20part%20file%20perhaps%20it%20is%20more%20useful%20it.%20The%20file%20itself%20contains%20around%2020%2C000%20rows%20so%20it%20is%20quite%20time-consuming.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297353%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297353%22%20slang%3D%22en-US%22%3E%3CP%3Eagreed%20on%20the%20filter%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616599%22%20target%3D%22_blank%22%3E%40Drin365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20test%2C%20data%20filters%20does%20not%20identify%20the%20picture%2Fimage.%3C%2FP%3E%3CP%3EI%20also%20tried%20a%20pivot%20table%2C%20still%20does%20not%20recognize%20the%20picture%2Fimage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20take%20it%20back%2C%20sort%20did%20not%20work%20when%20sorting%20on%20the%20image.%20Darn.%20I%20must%20have%20had%20my%20mouse%20in%20A2.%20Sorry%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20all%20I%20got.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297364%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297364%22%20slang%3D%22en-US%22%3E%3CP%3Eok%20one%20more%20question%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616599%22%20target%3D%22_blank%22%3E%40Drin365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20a%20lot%20of%20records%20and%20probably%20a%20lot%20of%20images.%3C%2FP%3E%3CP%3EI%20wonder%20about%20the%20source%20of%20the%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWas%20it%20pulled%20from%20somewhere%20that%20might%20have%20the%20option%20to%20export%20additional%20data%20that%20might%20include%20some%20other%20field%20related%20to%20the%20image%20name%2C%20or%20anything%20like%20a%20flag%20that%20might%20identify%20the%20items%2Fproducts%20that%20have%20images%20on%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cringe%20at%20the%20thought%20that%20someone%20sat%20there%20and%20hit%20copy%20and%20paste%20to%20line%20it%20up%20for%20each.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297371%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297371%22%20slang%3D%22en-US%22%3EIt%20was%20sent%20to%20me%20like%20that%20from%20a%20supplier%20so%20that's%20the%20only%20data%20I%20have.%20Not%20sure%20but%20might%20have%20used%20the%20Excel%20Image%20Assistant%20to%20insert%20pics.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297427%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%2Ffilter%20cells%20with%20picture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297427%22%20slang%3D%22en-US%22%3EI%20found%20the%20below%20VBN%20methods%2C%3CBR%20%2F%3E%3CBR%20%2F%3EMETHOD1%3CBR%20%2F%3E%3CBR%20%2F%3EFunction%20CellImageCheck(CellToCheck%20As%20Range)%20As%20Integer%3CBR%20%2F%3E'%20Return%201%20if%20image%20exists%20in%20cell%2C%200%20if%20not%3CBR%20%2F%3EDim%20wShape%20As%20Shape%3CBR%20%2F%3EFor%20Each%20wShape%20In%20ActiveSheet.Shapes%3CBR%20%2F%3EIf%20wShape.TopLeftCell%20%3D%20CellToCheck%20Then%3CBR%20%2F%3ECellImageCheck%20%3D%201%3CBR%20%2F%3EElse%3CBR%20%2F%3ECellImageCheck%20%3D%200%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20wShape%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3E%3CBR%20%2F%3EMETHOD2%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20CellImageCheck()%3CBR%20%2F%3EDim%20checkRange%20As%20Range%3CBR%20%2F%3EDim%20x%20As%20Shape%3CBR%20%2F%3EDim%20flag%20As%20Boolean%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20checkRange%20%3D%20Application.InputBox(%22please%20enter%20one%20cell%20that%20you%20want%20to%20check%22%2C%20%22CellImageCheck%22%2C%20Selection.Address%2C%20%2C%20%2C%20%2C%20%2C%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EIf%20checkRange%20Is%20Nothing%20Then%20Exit%20Sub%3CBR%20%2F%3Eflag%20%3D%20False%3CBR%20%2F%3EFor%20Each%20x%20In%20ActiveSheet.Shapes%3CBR%20%2F%3EIf%20x.TopLeftCell.Address%20%3D%20checkRange.Address%20Then%3CBR%20%2F%3Eflag%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EIf%20flag%20Then%3CBR%20%2F%3EMsgBox%20%22Found%20an%20image!%22%3CBR%20%2F%3EElse%3CBR%20%2F%3EMsgBox%20%22No%20Image%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMethod%201%20it%20doesn%E2%80%99t%20work%2C%20all%20results%20are%201%20irrelevant%20is%20there%20is%20or%20isn%E2%80%99t%20a%20picture%20unless%20there%20is%20a%20text.%3CBR%20%2F%3E%3CBR%20%2F%3EMethod%202%20is%20working%20fine%20but%20obviously%2C%20it%20doesn%E2%80%99t%20make%20sense%20to%20use%20is%20it%20takes%20longer%20the%20checking%20manually.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20someone%20help%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I have an excel sheet some cells have picture others don't is there a quick way to sort or filter the items which have pictures?

 

Thanks in advance.

Capture.JPG

7 Replies
Highlighted
Is the image literally embedded in the cell? Otherwise, sorting would only rearrange column A. If the images are something like a webding/wingding, you should be able to sort by that. And if you wanted to control the sort order, you could use column C to provide a numerical value to sort them in a specific order, and you could assign the # via a formula you could quickly copy and paste down. However, this could be timely though if you have a large variety of images. Just a couple ideas.
Highlighted

If the image fits within the cell, it might actually sort with the row @Drin365 

 

Just click in cell B2, go to the DATA tab, and select the easy sort icon for Z to A and see what happens.

You can also click undo or close the file without saving if the results are not as expected.

 

Again, as I mentioned, if you want to control the sort of the picture you may need to do some kind of formula if you can. Unless the row count is low enough for you to eyeball it (I would wait until after you sort so the similar images are together). 

Highlighted

@HCole718 

 

First of all thanks for your replay. Yes, I tried with the filter but I have nothing to sort with. I’m attaching part file perhaps it is more useful it. The file itself contains around 20,000 rows so it is quite time-consuming.

Highlighted

agreed on the filter @Drin365 

In my test, data filters does not identify the picture/image.

I also tried a pivot table, still does not recognize the picture/image.

 

I take it back, sort did not work when sorting on the image. Darn. I must have had my mouse in A2. Sorry,

 

That's all I got. 

Highlighted

ok one more question @Drin365 

 

That's a lot of records and probably a lot of images.

I wonder about the source of the data. 

 

Was it pulled from somewhere that might have the option to export additional data that might include some other field related to the image name, or anything like a flag that might identify the items/products that have images on file?

 

I cringe at the thought that someone sat there and hit copy and paste to line it up for each.

 

Highlighted
It was sent to me like that from a supplier so that's the only data I have. Not sure but might have used the Excel Image Assistant to insert pics.
Highlighted
I found the below VBN methods,

METHOD1

Function CellImageCheck(CellToCheck As Range) As Integer
' Return 1 if image exists in cell, 0 if not
Dim wShape As Shape
For Each wShape In ActiveSheet.Shapes
If wShape.TopLeftCell = CellToCheck Then
CellImageCheck = 1
Else
CellImageCheck = 0
End If
Next wShape
End Function

METHOD2

Sub CellImageCheck()
Dim checkRange As Range
Dim x As Shape
Dim flag As Boolean
On Error Resume Next
Set checkRange = Application.InputBox("please enter one cell that you want to check", "CellImageCheck", Selection.Address, , , , ,
If checkRange Is Nothing Then Exit Sub
flag = False
For Each x In ActiveSheet.Shapes
If x.TopLeftCell.Address = checkRange.Address Then
flag = True
End If
Next
If flag Then
MsgBox "Found an image!"
Else
MsgBox "No Image"
End If
End Sub


Method 1 it doesn’t work, all results are 1 irrelevant is there is or isn’t a picture unless there is a text.

Method 2 is working fine but obviously, it doesn’t make sense to use is it takes longer the checking manually.

Can someone help?