Previous Topic Next Topic

Copy data from one sheet 1 to sheet 2 only if text

Closed
Loo0105 - Aug 10, 2016 at 01:31 PM
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 - Aug 14, 2016 at 01:53 AM
Hello,

I have sheet 1 tracking a lot of different information (Client name, date in, date put etc...) for 2 machines. (Machine A & Machine B)

Now I want to separate the machines into additional sheets. Let's say Sheet 2 for Machine A only.

I will continue to only update sheet 1. However every time I enter Machine A in its column on sheet 1 I want all the information for that client to transfer automatically to sheet 2. Otherwise I don't want it to do anything. I don't want blank rows etc..

If I use conditional formatting and an IF formula it gives me blank rows. Please help!

Related:

1 response

Answer 1 / 1
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 1
Aug 14, 2016 at 01:53 AM
Conditional Formatting only affects the cosmetics of a cell, not its contents :)

1 quick way to do this would be with a helper column. 

Prod	Qty	Helper		Prod	Qty
aa	1	aa 1		aa	1
bb	2	bb 1		aa	4
cc	3	cc 1		aa	7
aa	4	aa 2			
bb	5	bb 2			
cc	6	cc 2			
aa	7	aa 3			
bb	8	bb 3			
cc	9	cc 3

C2 (3rd column)=A2&" "&COUNTIF($A$2:A2,A2)
copied down.

Then to do the extract...
E2=IFERROR(INDEX(A:A,MATCH("aa "&ROWS($1:1),$C:$C,0)),"")
copied down and across as needed
0