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!

1 reply

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