Is there a formula that I can pull down in column B, that takes this in column A

A
1
2

4

5
6

7

8

And return this in column B?

B
1
2
4
5
6
7
8

Thanks,
Howard

Hi Howard,

Am Wed, 26 Nov 2014 20:32:02 -0800 (PST) schrieb L. Howard:

Is there a formula that I can pull down in column B, that takes this in column A
A
1
2

4

5
6

7

8
And return this in column B?
B
1
2
4
5
6
7
8

why don't you sort the column?
Or use:
=SMALL(A:A,ROW(A1))
or
=LARGE(A:A,ROW(A1))
and copy down


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

I can see now I did not give enough or proper information.

With a long list of data in column A, I have a macro that returns results to column B.

The B results are next to certain data in column A and acts to show where some sought after criteria was found. Along with a value from column A.

So strewn down column B (1200 rows) are returns that may look like "1x2 = 6", "3x2 = 6" and the such. These are all text and any resemblance to a formula coincidental, and where the 6 is the cell where the sixth occurrence of that value appeared in column A.

Those need to stay put to mark where in column A they are and I want a consolidated list of all those returns in column C, for easier viewing of the results.

So I cannot sort column B. I thought if I had a formula I could use it in the code to throw into column C with a .Value = .Value and do whatever I need to on column C.

All the returns in B are .Value = .Value results (constants) so it may be easier to deal directly with them.

Now, that makes me think I should just copy B to C and sort C.

Hmmm, I should have thought of that sooner. Sorry.

Howard

Hi Howard,

Am Thu, 27 Nov 2014 00:06:57 -0800 (PST) schrieb L. Howard:

I can see now I did not give enough or proper information.

With a long list of data in column A, I have a macro that returns results to column B.

please send me a little example with the expected result and a comment
with the cause for that output.
At the moment I don't understand the problem


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

O

please send me a little example with the expected result and a comment
with the cause for that output.
At the moment I don't understand the problem


This may help

With 4 in F1 finds 4 consecutive entries and marks in B.

https://www.dropbox.com/s/zxcfvdu3xg...ok11.xlsm?dl=0

Hi Howard,

Am Thu, 27 Nov 2014 02:58:21 -0800 (PST) schrieb L. Howard:

With 4 in F1 finds 4 consecutive entries and marks in B.

try:

Sub AnyDupesNumF1()
Dim i As Long, lr As Long, j As Long, k As Long
Dim vArray As Variant, varOut() As Variant

[B:B].ClearContents

With Sheets("Sheet2")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
vArray = .Range("A1:A" & lr)
k = .Range("F1")

For i = 2 To UBound(vArray)
If vArray(i, 1) = vArray(i - 1, 1) Then
j = j + 1
If j = k Then
.Cells(i - 1, 2) = vArray(i, 1) & " = " & j
j = 0
End If
Else
j = 0
End If
Next 'i

lr = .Cells(Rows.Count, 2).End(xlUp).Row
vArray = .Range("B1:B" & lr)
k = 0

ReDim Preserve varOut(WorksheetFunction.CountA(.Range("B:B")) - 1, 0)
For i = LBound(vArray) To UBound(vArray)
If Len(vArray(i, 1)) 0 Then
varOut(k, 0) = vArray(i, 1)
k = k + 1
End If
Next
.Range("C1").Resize(k) = varOut
End With
End Sub

And insert normal prodedures (Subs) in standard modules and not in sheet
modules


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

And insert normal prodedures (Subs) in standard modules and not in sheet
modules


Hi Claus,

Spot on as usual.

Thanks again.

Howard

Hi again,

Am Thu, 27 Nov 2014 03:50:31 -0800 (PST) schrieb L. Howard:

Thanks again.

you are welcome

You can also try in C1:

=IFERROR(INDIRECT("B"&SMALL(IF(B1:B2000<"",ROW(1: 2000)),ROW(A1))),"")
and insert the formula with CTRL+Shift+Enter and copy down


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional