If with individual Number

[jezza25]

Hi

I have the following number in a cells, I am looking to copy them over via a formula to create individual numbers with no duplicates.

Lets say in first column is the following numbers

1
1
2
2
2
3
3
4
4
4
4

I am looking to write a formula so in the next sheet all I have is the following

1
2
3
4

I had the following formula Cell A1 of next sheet = =Sheet2!A1
In Cell A2 I had this formula =IF(Sheet2!A2=A1,Sheet2!A2, Sheet2!A3)

This worked until I had the number occuring more than twice in the column

Thanks in advance

Jezza

[cbatrody]

Hi,

Please try the following formula in Sheet2 A1

=IFERROR(VLOOKUP(ROW(),Sheet1!$A$1:$A$11,1,FALSE),"")

[mehmetcik]

You will need an array formula.

https://www.youtube.com/watch?v=vAEPXVf3uOs

[cbatrody]

Added a helper column in the attached file and now the formula works even if there are missing numbers in between. However, you need to SORT the numbers A-Z before you apply this.

See if it works for you, otherwise you may have to opt for an array formula as suggested by mehmetcik

[Richard Buttrey]

Hi,

Why not just select the range and use the 'Remove Duplicates' functionality?

[6StringJazzer]

jezza25, in your example, the numbers are strictly ascending and strictly sequential. Is that always true in your data? Could you have data that skips numbers, or lists them in a different order?

[jezza25]

Thanks CBATRODY,

I sort of works I think it is the start rows I am having a issue with, below is a sample of actual numbers, the first number actually appears in cell C6 and the formula on the next sheet starts on A6. I seem to miss the first 5 using attached formula

8746925
8746925
8746926
8746926
8746927
8746927
8746928
8746929
8746930
8746931
8746933
8746942


Jezza

[jezza25]

Hi Richard, Because I want the person to drop a sheet in and it could have 1000's of rows, the less steps for them the better

[cbatrody]

Hi,

See the attached file, I have modified it as per your requirement.