Copy values

[tleilax]

Hello,

I attached excel file.
Sheet 1 is input data.
Sheet 2 is output data.

Is there any way how to automatically generate column A on sheet 2? In next step I would retrieve numbers for column B.

Thanks for any help and sorry for my bad english, I hope you can understand it.

[Chandrajit]

Hi tleilax,

The attached file should help you!excel_help_upload.xlsx

[tleilax]

Hi,

thank you. Any chance of getting rid of the "0" in D4? Why does it even show that 0 by the way?

[Fotis1991]

Hi

Do you have empty rows(like 11 and 19, in your example), in your real workbook?

If no empty rows, there is a formula to give you your result, without 0.

[tleilax]

Hi,
well, there are, but it's not necessary to have them, it's just for easier orientation - they are made by macro, so I don't need to use it.

So, using formula by chandrajit when I don't have empty rows will make the trick?

[Fotis1991]

Hi

In this case, a formula of my friend Zbor, will gives you, your result, without 0.

So in D3 and copy down, put this.

Please Login or Register  to view this content.

Hope, that helps you.

[tleilax]

I'm having real trouble copying your formulas to my real excel sheet.

In my real excel sheet the unique values should be in column K.
K1 - header, K2-K999999 values.

I read something about ctrl-shift-enter, could you maybe tell more details about how to work with your formulas?

So when I try to change column values in the formula from D to K and then use the formula, it says something about cycling the formula in loop (sorry, my excel is localized to my native language, so can't tell you the exact error) and after pressing OK (confirming the loop), it gives me value "0".

[Fotis1991]

Hi, again.

CSE, that you read, is for Array formulae. This is not an Array formula.

So, pay attention in the formula and you will do it.

Take a look to the sample.

[Chandrajit]

hi my formula will give 0 if the array contains empty rows. the formula is dynamic and will give all the unique values in the list!
and yes the formula is ctrl+shift+enter
to avoid the 0 you can either remove the empty rows or tweak your code to remove the empty rows