Generate new table

[zorans]

Hi,
here is my problem
(sorry if it is already mentioned,
I could't find it)

I have big table and I want to generate new table
but extracting only those rows which fulfill certain criteria

Example:
First table:
A 20
B 40
C 25
D 36

New table:
condition: rows with entry higher than 30

B 50
D 36

How can I do that?

Thank you for assitance!

Best regards
Zoran

 

[Peter_SSs]

zorans

Try looking up AutoFilter in the Excel Help and see if you think that might help.

 

[zorans]

I want new table

Thanks for your effort!

Is it possiblle to make some kind of report in another table?

Thks

Zoran

 

[Peter_SSs]

What sort of report do you have in mind? Example of data and report?

 

[zorans]

Data in row

Report should show all data in row,
according to criteria

To be more specific,
new table will be in different sheet, and my manager
would look only that sheet,
not messing with database

 

[Peter_SSs]

OK, let's see if this is headed in the right direction?

Data Sheet looks like this.

Mr Excel.xls
	A	B	C	D
1	A	20
2	B	40
3	C	25
4	D	36
5	E	89
6	F	20
7	G	31
8
Data Sheet

Now, on the Result Sheet I have some 'helper' data as well as the result table:

Enter the number you want to compare to (30 in your original example) in B1 (green in my example).

B2: =COUNTIF('Data Sheet'!B:B,">"&B1)
B3: =COUNT('Data Sheet'!B:B)
C1 (copied down at least the number of rows given by the value in B3): =IF(ROW()>$B$3,"",IF(INDEX('Data Sheet'!B:B,ROW())>$B$1,ROW(),""))
F1 (copied across 1 column and down at least the number of rows given by the value in B2): =IF(ROW()>$B$2,"",INDEX('Data Sheet'!A:A,SMALL($C:$C,ROW())))

Mr Excel.xls
	A	B	C	D	E	F	G	H
1	Lower Limit:	30			Results:	B	40
2	No. greater than lower limit:	4	2			D	36
3	Table length on Data Sheet:	7				E	89
4			4			G	31
5			5
6
7			7
Result Sheet

 

[zorans]

so far so good

Seems to me that is what I've been looking for!

Thank you!

Regards

zoran