Help Creating List

billpq

Board Regular
In rows 1 thru 50 of Column A the cell will contain a number from 1 to 10. Next to it in Column B is an unrelated unique number. What I would like to do starting in cell D1 is list the cell value of Column B for the corresponding row of the first time a "1" appears in Column A. In cell D2 would be Column B's value for the 2nd occurence of "1" in Column A, cell D3 would have the 3rd occurence, thru cell D5. In cells E1:E5 would be the same idea for "2" in Column A. Cells F1:F5 would be for "3", etc until cells M1:M5 which would be for values in Column B where a "10" was in Column A.

I know I can find a way to do this with Filters, but I'm hoping to find a way with just a straight formula or custom function. Somehow I think I need to learn more about Index and Indirect, but I keep getting confused when I try.

Any help would be greatly appreciated!

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Well-known Member
Given this data series:
Excel Workbook
AB
1462
210-21
3848
4681
54-91
6455
7674
81-49
92-54
109-46
1110-92
12165
137-68
14191
156-15
16321
171-95
189-21
195-65
20339
Sheet1

Use this formula (header rows in D1:M1, formula from D2 copied down and across):
Excel Workbook
DEFGHIJKLM
112345678910
2-49-542162-6581-6848-46-21
365#NUM!39-91#NUM!74#NUM!#NUM!-21-92
491#NUM!#NUM!55#NUM!-15#NUM!#NUM!#NUM!#NUM!
5-95#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Sheet1

venkat1926

Well-known Member
data in column A and B starts from row 2 down without any blank

with these assumptions the following macro may be useful

Code:
``````Sub test()
Dim rng As Range, cfind As Range, x() As Integer, j As Integer
Dim add As String, k As Integer, dest As Range, m As Integer
Set dest = Range("A2")
m = 0
Set rng = Range(Range("a2"), Range("A2").End(xlDown))
With rng
For j = 1 To 10

k = WorksheetFunction.CountIf(rng, j)

'MsgBox k
ReDim x(k)
On Error Resume Next
Set cfind = .Cells.Find(what:=j, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
m = m + 1
x(m) = cfind.Offset(0, 1).Value
'MsgBox x(m)
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then GoTo line2
m = m + 1
x(m) = cfind.Offset(0, 1).Value
'MsgBox x(m)
Loop
line2:
For m = 1 To k
'MsgBox dest.Offset(m - 1, j + 2).Address
dest.Offset(m - 1, j + 2) = x(m)
Next m
line1:
m = 0
Next j
End With
End Sub``````

billpq

Board Regular
Thank you both very much for your help!

Sal, I tried your suggestion first and it works great! That is just what I was looking for. My only problem is now I need to "relocate" this to another area of the worksheet and when I tried adjusting the ranges in the formula I got incorrect results. Would you mind showing me another version of the formula where the original data range of A1:B20 is now the range D4:E23 (on Sheet1) and the results will go on Sheet2 cells B8:K8 (header) and formulae to be pasted into cells B9:K13 of Sheet2.

Hopefully from seeing this version I will be able to understand better how it works and then I will be able to adjust myself in the future!

Thank you very much!

Well-known Member
Try this:
Code:
``{=INDEX(Sheet1!\$E\$4:\$E\$23,SMALL(IF(Sheet1!\$B\$4:\$B\$23=B\$8,ROW(\$B\$4:\$B\$23)),ROW()-8))}``
Paste in Sheet2!B9 and copy down.

To understand it, I suggest you copy my original example into a sheet.

Copy this formula into a cell and confirm with Ctrl-Shift-Enter:
=IF(\$A\$1:\$A\$20=D\$1,ROW(\$B\$1:\$B\$20)

Click in the formula bar, and hit the F9 key. You will see:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;FALSE;FALSE;FALSE;12;FALSE;14;FALSE;FALSE;17;FALSE;FALSE;FALSE}

That is the result of the formula. Why? Because it is checking A1:A20 for 1. If there is a 1, it returns the row number. If there isn't a 1, it returns FALSE. Since the first 1 is in A8, it returns 7 falses, and then the 8th row enters an 8, 3 more falses, then row 12, one false, row 14, etc.

So to continue the example, this formula gives the row numbers for rows with a 1 (when a 1 is in cell D1):
=IF(\$A\$1:\$A\$20=D\$1,ROW(\$B\$1:\$B\$20))

INDEX() returns a certain value in a range. In this case, INDEX is returning a value from the previous array of row numbers, and taking the corresponding row from B1:B20. The SMALL() function will take the nth smallest value from an array, which is determined by ROW()-1 (since we start from row 2, and we want the #1 smallest value), and then look it up in B1:B20:
=INDEX(\$B\$1:\$B\$20,SMALL(IF(\$A\$1:\$A\$20=D\$1,ROW(\$B\$1:\$B\$20)),ROW()-1))

Presto.

Hope it makes sense somewhat.

billpq

Board Regular
Sal -

That works! Thank you very much for the help & explanation... I get it now!

- Bill

Replies
23
Views
2K
Replies
4
Views
359
Replies
1
Views
373
Replies
3
Views
3K
Replies
3
Views
205

1,195,939
Messages
6,012,425
Members
441,698
Latest member
DaveTeo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back