Automatic populate Sheet2 with data from Sheet1 based on criteria.

[Saurabh Khanna.]

Hello,

I have two Worksheets Sheet1 and Sheet2.
I want to automatically fill up the Sheet2 with the records copied from the
Sheet1 with selected columns.

For example, Sheet1 looks like following:
A B C D
R1 1 3 1 1
R2 2 2 2 3
R3 1 1 2 1
R4 4 3 5 4

Now blank Sheet2(to be automatically populated) looks like following:
A B
R1
R2
R3
R4

What I want is that Records from the Sheet1 should be selected based on
criteria and Sheet2 to be filled automatically with selected columns.

Example:
Records should be searched in Sheet1 where if a row is having column B's or
column C's value as "2", then Sheet1's respective row's columns to be
populated in Sheet2's column(let us suppose only cloumn A and B) for every
qualifying record, And I should get Row2 and Row3(which matches this
condition) as records in Sheet2 populated.

How can I get this, or which formula approach I can opt?

Thank You,
Saurabh Khanna.

 

[Pete_UK]

Why can't you just apply autofilter to Sheet1 for the criteria you are
interested in? (eg Column C, value 2). Then you can copy just the
visible data to Sheet2.

Hope this helps.

Pete

 

[Max]

One formulas play which will deliver it here

Assume source data in Sheet1's cols A to D, data from row2 down

In Sheet2,
Assume the criteria values for the source column (letter) and its chosen
numeric value will be entered in A1:A2, eg
In A1: B
In A2: 2
(ie column B, lines with numeric value 2)

Put in C2:
=IF(INDIRECT("'Sheet1'!"&$A$1&ROWS($1:1)+1)=$A$2,ROW(),"")
Leave C1 blank

Put in D2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))))
Copy D2 across to G2. Select C2:G2, fill down to cover the max expected
extent of source data in Sheet1. Minimize col C. Cols D to G will return the
required results from Sheet1 for the criteria inputs in A1:A2, with all lines
neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik