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

[Closed]
Report
-
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
-
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

Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
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