cleanse data and count the unique types of container sizes

I need to count the unique types of container sizes on a sheet of data that is pasted in to a sheet called paste.
Sheet 1 has the container types sizes I would like to count

However there is little control over the pasted data and it needs cleansing before counting.
Some are recorded as 8' x 6' instead of 8 X 6 so I will need to remove the apostrophes, some have small x instead of capital X.
I don’t want strikethrough lines counted. Perhaps extracting and counting just the size types would be the way to start ?

I have a sumproduct formulae counting the containers
But am not convinced this is the best way to go.

Any ideas please

Hi Mike

Getting compile error
type declaration does not match declared data type?

Try

Please Login or Register  to view this content.

Thanks jindon
Thats a good start
can we trim it to just leave the sizes ?

yes Mike, with the example file.
Getting compile error
type declaration does not match declared data type?

wow, very good
but the sizes would be good to start with
so
6 X 6 QTY 7
7 x 6 QTY 4
12 x 6 QTY 1 etc

Not really sure though..,

Please Login or Register  to view this content.

Get a runtime error 9 at this line

Please Login or Register  to view this content.

Are you talking about the same file?

Ok dont know what happened there

The last one is getting there, but I basically want to drop all the text descriptions so I am left with just sizes

If this is not what you want, upload a workbook with your EXACT desired result.

Please Login or Register  to view this content.

Hi Jindon
I have attached a file with desired outcome.

This should do.

Please Login or Register  to view this content.

Spot on jindon , many thanks