extracting data of ROW by conditional check of one column

[Ravi kumara]

I am using excel 2007 and 2013 in windows xp and 7, From the below sample data in sheet1 of workbook1- If "C1" is present in column G1 - G10 , then all the data correspanding to that ROW containg "C1" should be written in other sheet of new workbook. Is it possible if so solve my problem.
	Data in sheet1 of workbook1
	A	B	C	D	E	F	G
	332	07/11/2014	CDPO	24/10/2014	950	Office shifting	C1
	333	07/11/2014	DC	31/10/2014	-	Jeep KA-35 G-45	KHR
	334	07/11/2014	AEE, QC	17/10/2014	-	work regarding	ASO
	335	07/11/2014	Prinicpal, ITI	29/10/2014	-	Training programme	C1
	336	07/11/2014	AC	29/10/2014	-	tri-month report	C1
	337	07/11/2014	DC	23/10/2014	887	Fees payment	ASO
	338	07/11/2014	EE,PWD	31/10/2014	1083	building safty	ASO
	339	07/11/2014	EE,PWD	31/10/2014	1072	construction of building	KHR
	340	07/11/2014	EE,PWD	31/10/2014	1082	work regarding	KHR
	341	07/11/2014	EE,PWD	31/10/2014	1077	work regarding	KHR
	Result in sheet1 of workbook2
	332	07/11/2014	CDPO	24/10/2014	950	Office shifting
	335	07/11/2014	Prinicpal, ITI	29/10/2014	-	Training programme
	336	07/11/2014	AC	29/10/2014	-	tri-month report

<tbody>
</tbody>

 

[SteveO59L]

will the action value always be c1?

 

[Ravi kumara]

"C1" in column G1 to G10 is not the cell reference, it is the text. the data in A1 to G10 is manually entered by data operator. From that data i need contents of all rows of which contains data(text) "C1" only.

 

[Peter_SSs]

Assuming sheet1 of workbook1 is active when the code is run, try this with a copy of your workbook.

Sub CopyData()
  Application.ScreenUpdating = False
  ActiveSheet.Copy
  With ActiveSheet
    .Rows(1).Insert
    .Range("G1").Value = 1
    .Columns("G").AutoFilter Field:=1, Criteria1:="<>C1"
    .UsedRange.EntireRow.Delete
    .Columns("G").ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[Ravi kumara]

no its not working. tell me to do in same work book (different sheet) only.

 

[Peter_SSs]

no its not working.

That gives me nothing to go on.
Did it give an error message? If so, details please.
Did it do nothing?
Did it put the wrong values into the new workbook? If so, some examples/details please.
Did it put the right results but in the wrong layout? If so, details please.

tell me to do in same work book (different sheet) only.

That is a change to your original request. I will address that when I have enough details regarding my other questions above.

... the data correspanding to that ROW containg "C1" should be written in other sheet of new workbook.

 

[Ravi kumara]

how to link the workbook1 with this ? i copyed code in workbook2. and it did not done nothing.

 

[Stefyu89]

this is a formula who can answer your request :

=IF(ROWS(A$17:A17)>COUNTIF($G$1:$G$10,$B$14),"",INDEX(A$1:A$10,AGGREGATE(15,6,(ROW($A$1:$A$10)-ROW($A$1)+1)/($G$1:$G$10=$B$14),ROWS(A$17:A17))))

"C1" has been inputed in range "b14" and data are placed in range A1:G10. You can expand this formula all over the the columns which interest you and in any row you want.

Best regards from France,

 

[Peter_SSs]

how to link the workbook1 with this ? i copyed code in workbook2.

I can't keep up with the changes. In post #1 it was two workbooks, in post #5 it was one workbook, now it is back to two workbooks.

Please give the following information:
1. The name of the workbook the original data is in.
2. The name of the worksheet that the original data is in.
3. The name of the workbook that the results should go in.
4. If the answer to 3. is a different answer to 1. then does the workbook you named in 3. already exist or does the code need to create it?
5. The name of the worksheet that the results should go in.
6. Does the worksheet you named in 5. already exist or does the code need to create it?

.. and it did not done nothing.

I'm having trouble believing that. The first thing the code does is make a copy of the active worksheet, creating a new workbook with just that worksheet in it. Are you saying that not even that happens?

 

[Ravi kumara]

here is the information i am giving u what u required

Information u required	Information I am giving
1. The name of the workbook the original data is in.	Inword_2014-15 (existing)
2. The name of the worksheet that the original data is in.	april, may, june, july, augest…..(for each completed month) and dec, jan, feb…(after completion of each month these are created by operator and data are entered in that month)
3. The name of the workbook that the results should go in.	Inword_2014-15_emp wise (New one to be created)
4. If the answer to 3. is a different answer to 1. then does the workbook you named in 3. already exist or does the code need to create it?	code need to create
5. The name of the worksheet that the results should go in.	same as in question no.2 but it should be in new workbook ( i.e in "Inword_2014-15_emp wise")
6. Does the worksheet you named in 5. already exist or does the code need to create it?	code need to create

<tbody>
</tbody>