Automatically extract only relevant data from a worksheet into a new one?


M

MCSmarties

Hello,
I need to retrieve data matching particular criteria from a worksheet.
All "matching" cells should be inserted in a new worksheet
(essentially a "cleaned" sheet containing ONLY the relevant data)
All cells that do not match the criteria should be "skipped"
completely, NOT just left blank.
It needs to be done automatically, not requiring any user interaction
(so data sorting won't work)

Example: I have data in the following form in Worksheet 1

A x1 x2 x3 x4
B y1 y2 y3 y4
C x1 x2 x3 x4
D z1 z2 z3 z4
E x1 x2 x3 x4
F u1 u2 u3 u4

I'm only interested in the data of type "x" (so only rows A, C, E
match).
I want Worksheet 2 to contain ONLY this relevant data:

A x1 x2 x3 x4
C x1 x2 x3 x4
E x1 x2 x3 x4

eg WITHOUT empty cells for the "non-matching" data.

If Worksheet 1 is modified, Worksheet 2 should reflect the changes
automatically
(for example if the data in row C is replaced with data of type "w",
Worksheet 2 should
only contain:

A x1 x2 x3 x4
E x1 x2 x3 x4

Skipping individual cells would be even better! eg:

Worksheet 1:

A x1 y2 x3 x4
B y1 x2 x3 y4
C y1 y2 y3 y4

to

Worksheet 2:

A x1 x3 x4
B x2 x3

Can this be done, ideally while only using formulae?
Execution speed is not really an issue.
( I used rows for this example, data could also be in columns)
 
Ad

Advertisements

M

Max

One formulas play which would deliver it here ..
(neat bunching of extracts to the left and at the top in the results sheet)

Assume source data in sheet: S, cols A to D, from row1 down

Assume the extraction criteria for each row in S
is simply the leftmost character (eg: x)

In a new sheet: 1,
In A1: =IF(LEFT(S!A1)="x",COLUMN(),"")
Copy across to D1, fill down to cover the max extent of data in S

In a new sheet: 2,
In A1: =INDEX(S!1:1,SMALL('1'!1:1,COLUMNS($A:A)))
Copy across/fill down to the same extent as done in sheet: 1

(Hide away sheets: 1 & 2)

Then in a new sheet: R, (R=results)
In A1:
=IF(ISERROR('2'!A1),"",ROW())

In B1:
=IF(OR(ROW()>COUNT($A:$A),ISERROR(INDEX('2'!A:A,SMALL($A:$A,ROW())))),"",INDEX('2'!A:A,SMALL($A:$A,ROW())))
Copy B1 to E1. Select A1:E1, Copy down to the same extent as done in sheet:
1. Hide away col A. Cols B to E will auto-return the required results, with
all lines neatly bunched at the top and with data neatly bunched to the left
in each line.
 
M

MCSmarties

One formulas play which would deliver it here ..
(neat bunching of extracts to the left and at the top in the results sheet)

Assume source data in sheet: S, cols A to D, from row1 down

Assume the extraction criteria for each row in S
is simply the leftmost character (eg: x)

In a new sheet: 1,
In A1: =IF(LEFT(S!A1)="x",COLUMN(),"")
Copy across to D1, fill down to cover the max extent of data in S

In a new sheet: 2,
In A1: =INDEX(S!1:1,SMALL('1'!1:1,COLUMNS($A:A)))
Copy across/fill down to the same extent as done in sheet: 1

(Hide away sheets: 1 & 2)

Then in a new sheet: R, (R=results)
In A1:
=IF(ISERROR('2'!A1),"",ROW())

In B1:
=IF(OR(ROW()>COUNT($A:$A),ISERROR(INDEX('2'!A:A,SMALL($A:$A,ROW())))),"",INDEX('2'!A:A,SMALL($A:$A,ROW())))
Copy B1 to E1. Select A1:E1, Copy down to the same extent as done in sheet:
1. Hide away col A. Cols B to E will auto-return the required results, with
all lines neatly bunched at the top and with data neatly bunched to the left
in each line.
(...)

Thanks a lot for the quick reply and the awesome tip, it works!
However, the current setup _requires_ the original data to be starting
at A1.
I think it's because of the INDEX(S!1:1) part of the function in sheet
2.

How can I modify this to make it work for any range in sheet S?
eg, instead of having data from A1:D4 having it for example in G6:J10
(or whatever)
Ideally, one could move the data to somewhere else in S and sheet 2
(and hence sheet R) would be updated. It would make it much more
versatile!

I can of course explicitely define a range INDEX(S!G6:G6) and take it
from there,
but that kills the "adaptability" part. Maybe by using LOOKUP and
OFFSET to
find the initial data - but how can I do this?
 
Ad

Advertisements

M

Max

How can I modify this to make it work for any range in sheet S?

Assume source data is a defined range: MyR

In sheet: 1,

In A1:
=IF(LEFT(INDEX(MyR,ROWS($1:1),COLUMNS($A:A)))="x",COLUMN(),"")
Copy across/fill down to cover the extent of MyR

In a sheet: 2,

In A1:
=INDEX(T(OFFSET(MyR,ROWS($1:1)-1,COLUMN($A:$D)-1)),SMALL('1'!1:1,COLUMNS($A:A)))
Copy across/fill down to cover the extent of MyR

Sheet: R = no change to formulas
Just ensure the B1 copy across & row fill down covers the extent of MyR
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top