Row to 2nd sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have 10+ columns of data with 100+ rows.
And in Column C there are different codes (S, R, K, X).
I would like a formula that searches the columns and finds just the R coded
rows and then moves that "R" entire row of data to a 2nd sheet.
 
Hi David

One way
Apply an autofilter with Data>Filter>Autofilter
Use the dropdown on column C to select R
Mark the range of visible cells Cut>Paste to Sheet2
 
Here's a way to copy all lines (not move) into another sheet by the key col
value

Assume source data in Sheet1, cols A to J, data from row2 down, where the
key col = col C (eg: S, R, K, X, etc - only alphas assumed)

In Sheet2,
Enter the key value into A1, eg: R

Put in A2: =IF($A$1="","",IF(Sheet1!C2=$A$1,ROW(),""))

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
Copy B2 across 10 cols to K2. Select A2:K2, copy down to cover the max
expected extent of source data in Sheet1, eg down to K200?. Cols B to K will
return only the lines corresponding to the key value entered in A1, all
neatly bunched at the top.
 
Back
Top