copying table contents to other table based on certain criteria

[usmanhere]

HI every one!

can some body tell me how can we transfer the contents of the table to any other table if it fulfills the certain criteria.

e.g. if column A of one Table contains dates, and we wish to copy only those contents to other table which fall between particular range of dates. Just like below:

Table 1

Column A Column B Column C Column D

Row1 3/2/2012 ABC shjshja 634764248

Row2 2/12/2012 DEF sahgdy 217863

Row3 3/26/2012 EFG kuyewuij 21728136

Row4 1/15/2012 XYZ ewtuuewiu 23128978


Now we wish to copy/transfer/link only those rows from the above table to other table which falls between March 2012 (column A Dates). Howveer we do not want to copy column C in other table

 

[Hui]

Have a look at using Advanced Filter

 

[Luke M]

You could either setup an Advanced Filter, or a MS Query. Or, if you want a formula:


=IF(ROWS($A$2:$A2)>(COUNTIF($A:$A,">="&Data1)-COUNTIF($A:$A,">="&Date2)),"",INDEX(A:A,SMALL(IF(($A$2:$A$100>=Date1)*($A$2:$A$100<=Date2),ROW($A$2:$A$100)),ROW($A1))))


This is an array formula which would need to be confirmed using Ctrl+Shift+Enter. Copy it to the right as needed, and as far down as you would ever need. Note that you may need to delete one column of formulas if you don't want col C.

How the formula works:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[SirJB7]

Hi, usmanhere!


Give a look at this file, open it allowing macros, run macro CreateNewFilteredTable, and check the output:


http://dl.dropbox.com/u/60558749/copying%20table%20contents%20to%20other%20table%20based%20on%20certain%20criteria%20%28for%20usmanhere%20at%20chandoo.org%29.xlsm


Regards!

 

[usmanhere]

Thanks to all

I will try to both the things.

 

[prashant Priyadarshi]

Hello Everyone !


Suppose I ahve to do data entry in a Coloumn "A" and I don't want to do duplicate entry.

Please help me with best suitable way. I want to make some formate or use some commands the cell should not eccept duplicate entry and it will show a pop up window with error message.


I heard this from one of mine friend, can do this with data valadiation option, but I forgot. so please help me out.


Best regards,

Prashant Priyadarshi

 

[Luke M]

Prashant,

In the future, you should start your own thread, rather than tacking on the the end of someone else's (which is referred to as "thread hijacking").

To answer your question, however, a Data Validation custom formula (with cell A2 selected) to use would be:

=COUNTIF($A$1:$A1,A2)=0