Unique Values, Paste and Transpose

Hi,

I have a sheet which contains a large amount of data on it ("data") which lists customers in no particular order.

Is there any quick way in which I can have a code which automatically detects only unique customers and then once found it pastes them to a new sheet (called overview).

I have included an example of what I am trying to achieve as I can never explain myself too well.

If possible i would need it pasted to the overview sheet, cell I3 if possible?

Any questions please ask, thank you in advance to anyone that has time to look into this.

Paul

Use the advanced filter (in the Data tab on top) to filter and create a list of unique customer numbers. Then copy that list and PasteSpecial to the location on your new sheet. Choose transpose to get the data going horizontal like you want it to. You can pull in the customer names with a lookup function.

If you really need this automated, try using the macro recorder while you do the above process.

You can use array formulas to pull out unique values, but I strongly suspect it will run much slower than the advanced filter. The filter usually doesn't take too long, and if you automate it, the process should go fairly quick.

Have you tried the Macro Recorder as I suggested above?

Could you explain what you mean when you say you failed?

Excel often assumes your first cell is a header. Be sure you are selecting a header with your data when you do the advanced filter.

In your code, you will need to define a variable that will hold the number of values in the column. In general, you can find the last row containing values in a column with this line of code:

Please Login or Register  to view this content.

where "A" is the column.

If you post the code that the macro recorder gave you, I can help you tweak it to work for variable columns of data.

Ok, there are some ways we can potentially clean up that code to make it more efficient (in general, selecting cells/sheets is unnecessary when working in code), but first let's get it to work the way you want it. My first guess would be this:

Please Login or Register  to view this content.

I've changed the line that selects your filtered list to always go down to the last row with data (as we previously discussed), I put the destination sheet as "macro" like you said, and I changed the destination cell to A1. Does it work as you'd like it to?

I have a problem with the code :


"Run Time Error 1004

The infomration cannot be pasted because the copy area and paste area are not the same size and shape"

Do you know of a fix?

Any way you can post a sample workbook?

Try this, it specifies some of the code that refers to active objects. It also gets rid of some of the selecting:

Please Login or Register  to view this content.