A consolidating formula?
 

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

A consolidating formula?
 

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

A consolidating formula?
 

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

A consolidating formula?
 

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

A consolidating formula?
 

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

A consolidating formula?
 

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

A consolidating formula?
 

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


Hi Claus,

Spot on as usual.

Thanks again.

Howard

A consolidating formula?
 

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