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)

 

[Max]

Just to add on a little further ...

Not sure whether it might be a system resource issue
and / or Excel's worksheet? iteration? limitation(s)
but here's some suggestions in case you're finding it
a little tough to fill the array formula:

Before you fill the array formula across / down

Put Calc mode to "manual"
(via Tools > Options > Calculation tab > Check "Manual" > OK)

Press F9 to calculate after filling the formulas
(You'll have to wait a while for the heavy number crunching to complete)

-------------------
The array formula given was fill-tested
on a small sample of 100 rows x 6 cols (B1:G100)
and the returns were verified ok.

Extended "stress" testing the formula fill
in a grid of 6000 rows by 5 cols (B1:F6000)
was still successful
[30,000 formula cells]

But attempts to fill the formula
in a grid of 6000 rows by 6 cols (i.e. B1:G6000)
resulted in the formula filled only up to row5451 [cell E5451]
[that's around 32704 formula cells]

It just wouldn't fill further than that.

Experimentation with another sheet in the same book
revealed the same constraint viz.
a max? 32704 formula cells and that's it

So, if you have 6000+ rows and 6 cols or more to be filled
(i.e. cols B to G or more), consider:

Splitting the formula fill over several sheets
(since each sheet could apparently accommodate
up to 32704 of the "array formula fills" each) with say:

Sheet2's formulas checking cols B - D of Sheet1
Sheet3's formulas checking cols E - G of Sheet1
Sheet4's formulas checking cols H - J of Sheet1
and so on
(so as to keep each sheet within the "32,704" limit?)

And then collate & summarize from all these Sheets 2,3,4, etc
in yet another sheet via simple direct link formulas.

 

[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?