writing the columns a number is listed in

[Guest]

Thinking wise I could even create a formula to show what row the number was
found.

ie.
Row Number
----- ---------
100 1
101 2
102 3
103 2
104 3
105 1

Having the results show:
1: 100, 105
2: 101, 103
3: 102, 104

Either have it be Column A has the 1, 2,3 and Column B has 100, 101, 102 and
Column C has 105, 103, 104.... or the 100, 105 can be in the same column if
possible.

Does anyone know the formula to make this?
Any help would be appreciated.

 

[Max]

Here's one play ..

Assume this data is in Sheet1,
cols A and B, from row1 down

100 1
101 2
102 3
103 2
104 3
105 1

Put in D1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

Put in E1:
=IF(B1="","",COUNTIF($B$1:B1,B1))

Select D1:E1, fill down to cover the max expected data in cols A and B, say
down to E1000 ?

Put in F1: =MAX(E:E)-1
(make a note of the number in F1 for use in Sheet2)

In Sheet2
-------------
Put in the formula bar for A1:

=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"",INDEX(Sheet1!B:B,MATCH(SMA
LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy A1 down to A1000
(cover the same range as in Sheet1)

Put in the formula bar for B1:

=IF(ISNA(MATCH($A1&COLUMNS($A$1:A1),Sheet1!$B$1:$B$100&Sheet1!$E$1:$E$100,0)
),"",INDEX(Sheet1!$A$1:$A$100,MATCH($A1&COLUMNS($A$1:A1),Sheet1!$B$1:$B$100&
Sheet1!$E$1:$E$100,0)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy B1 across by as many cols as the number shown in F1 in Sheet1, say
across to C1, then fill down to C1000 to cover the same range as in Sheet1

For the sample data in Sheet1, you'll see that
cols A to C will return:

1 100 105
2 101 103
3 102 104

(rest are blank rows)

 

[Max]

Whoops, .. slight correction to the range refs in B1's formula:

Put in the formula bar for B1:

=IF(ISNA(MATCH($A1&COLUMNS($A$1:A1),Sheet1!$B$1:$B$100&Sheet1!$E$1:$E$100,0)
0&
Sheet1!$E$1:$E$100,0)))

Replace the formula in B1 with:

=IF(ISNA(MATCH($A1&COLUMNS($A$1:A1),Sheet1!$B$1:$B$1000&Sheet1!$E$1:$E$1000,
0)),"",INDEX(Sheet1!$A$1:$A$1000,MATCH($A1&COLUMNS($A$1:A1),Sheet1!$B$1:$B$1
000&Sheet1!$E$1:$E$1000,0)))

Array-enter as before

(with ranges corrected to 1000)