Home |
Search |
Today's Posts |
#1
|
|||
|
|||
writing the columns a number is listed in
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. |
#2
|
|||
|
|||
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) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "David" wrote in message ... 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. |
#3
|
|||
|
|||
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) ),"",INDEX(Sheet1!$A$1:$A$100,MATCH($A1&COLUMNS($A $1:A1),Sheet1!$B$1:$B$10 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) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting a number equal to writing commands | Excel Worksheet Functions | |||
identify numbers which are listed in two columns. | Excel Worksheet Functions | |||
Is there a maximum number of non-contiguous columns that can be h. | Excel Discussion (Misc queries) | |||
how to increase maximum number of columns in excel 2003 | Excel Discussion (Misc queries) | |||
Change number of columns mid page? | Excel Worksheet Functions |