Excel combinatorics/replace problem

[holloch]

Hello all,

I`m having a bit of trouble with the following problem. I have a larg
table (6000 plus rows) of this form:

x1 x4 x6 x8 x5
x2 x1 x9 x23 x11
x3 x9 x1 x44 x18 x101 x2
..............


I want all the values, i.e. xn, to be present in the table ONLY in th
row in which they occupy the first column. In other words, for x1 fo
example, I would like to delete all x1`s other than those in the firs
row. Since I`m dealing with a large table (and they`ll get larger) it`
very impractical to replace them one by one. Does anyone have an ide
how to do this faster ?

Thanks in advance!

Arnold B Nag

 

[Max]

Assume your table is in Sheet1, in A1:G6000

In a new Sheet2, say:

Put in A1: =Sheet1!A1
Copy A1 down to A6000
(this reproduces the 1st col, i.e col A of Sheet1, in Sheet2)

Put in B1: =IF(AND(Sheet1!B1<>$A$1:$A$6000),Sheet1!B1,"")
and *array-enter* the formula, i.e press Ctrl + Shift + Enter, viz.:

While holding down Ctrl + Shift keys, press Enter
(instead of just pressing Enter key alone)

Done correctly, Excel will wrap curly braces "{ }" around the formula, viz:
{=IF(AND(Sheet1!B1<>$A$1:$A$6000),Sheet1!B1,"")}

[Do not key-in the curly braces yourself]

Copy B1 across to G1, then copy down to G6000

B1:G6000 will return what you're after

If required, do a copy > paste special > check "values" > OK
on the table (A1:G6000) in Sheet2 either in-place or to another new sheet

 

[Max]

Correction, might have "misread" your specs a little.

Put instead in B1:

=IF(OR(AND(Sheet1!B1<>$A$1:$A$6000),Sheet1!B1=$A1),Sheet1!B1,"")

*Array-enter* the formula, i.e press Ctrl + Shift + Enter

Copy B1 across to G1, then copy down to G6000 (as before)

 

[Erich Neuwirth]

Please give more details for your problem.
Do you mean:
An element occuring in the first columns
should be deleted enywhere else in the table.

How about multiple occurences in the first column?
What happens to elements thet do not occur in the
first column at all.

Or should an element occuring in the first column be deleted
just in all rows below.
In that case, what happens if it occurs another time
in the first column also?
SHould it be deleted there or not?