Excel pattern fill 1

[khs999]

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

 

[1726]

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

 

[mintjulep]

I don't see a pattern.

 

[strongm]

>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.

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[khs999]

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

 

[SkipVought]

Did you try writing a macro?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[khs999]

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

 

[SkipVought]

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

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]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

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.

 

[khs999]

Thanks SkipVought, I'll give it a go

 

[combo]

Isn't the first answer a solution of the problem?

combo

 

[mintjulep]

Isn't the first answer a solution of the problem?0

It appears to be.

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]