Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel pattern fill 1

Status
Not open for further replies.

khs999

Technical User
Aug 3, 2011
11
GB
Does anyone know how I could auto generate a pattern in excel like this:

1 | 2
1 | 3
2 | 3
1 | 4
2 | 4
3 | 4
1 | 4
2 | 5
3 | 5
4 | 5

ie numerators and denominators of fractions in 2 individual columns?

When I try to fill down I don't get the pattern expanding itself ( 1 want all the way to 100, so don't want to type it by hand!)

Thanks
 
with the values 1 and 2 in cells A1 and B1 you can use the following formulas:

Cell A2: =IF(A1+1=B1,1,A1+1)
Cell B2: =IF(A1+1=B1,B1+1,B1)

You can then fill these formulas down the columns.

Afterwards, you can convert the formulas to values by copying the range containing the formulas into the clipboard and paste back their values.

Cheers
 
>I don't see a pattern.

I think you would if the second 1|4 had read 1|5, which is what it looks like it should have been.
 
hi,

I'd suggest a simple macro with 2 loops: the outside loop from 1 to 100 and the inside loop from 1 to a the value of the outside loop. The next column's valus is the value of the outside loop + 1.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Sorry, might help if I didn't have a typo!

1 | 2
1 | 3
2 | 3
1 | 4
2 | 4
3 | 4
1 | 5
2 | 5
3 | 5
4 | 5


Thanks
 
Did you try writing a macro?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Wouldn't know how to I'm afraid

No worries though - was hoping there would be an easy solution - am sure I can do it manually

Thanks anyway
 

Paste this into a MODULE in the VB Editor (alt+F11 toggles between the VB Editor and the Sheet, ctr+R makes the VB Project visible, right-click in the VB Project and select MODULE)

RUN this procedure
Code:
Sub test()
    Dim i, j, lRow
    
    lRow = 1
    
    For i = 1 To 100
        For j = 1 To i
            Cells(lRow, "A").Value = j
            Cells(lRow, "B").Value = i + 1
            lRow = lRow + 1
        Next
    Next
End Sub

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Mathematically I still don't see a pattern in either column by itself, or in the values of the fractions.

I can't imagine a formula solution that doesn't result in circular references.

Macro solution seems relatively straight forward.
 
Isn't the first answer a solution of the problem?

combo
 
Yes it appears to be, and 1726 deserves a [purple]*[/purple] !

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top