Copy rows from one sheet to another under certain conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-786084%22%20slang%3D%22en-US%22%3ECopy%20rows%20from%20one%20sheet%20to%20another%20under%20certain%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786084%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20can%20do%20this%20with%20excel%20formulas%2C%20but%20its%20really%2C%20really%20clunky.%20I%20imagine%20that%20VBA%20can%20do%20this%20easily%2C%20but%20I%20have%20no%20idea%20how%20to%20do%20VBA.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%3A%3C%2FP%3E%3CP%3EMove%20a%20row%20of%20data%20from%20the%20%22Raw%20Data%22%20sheet%20to%20the%20%22Undiluted%22%20sheet%20if%3A%3C%2FP%3E%3CP%3E---the%20%22Sample%20Type%22%20column%20says%20%22Unknown%22%20or%20%22Quality%20Control%22%3C%2FP%3E%3CP%3EAND%3C%2FP%3E%3CP%3E......the%20%22Dilution%20Factor%22%20column%20says%201.%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%3A%3C%2FP%3E%3CP%3Eif%20the%20dilution%20factor%20column%20says%20anything%20other%20than%201%20(i.e.%20%26gt%3B1)%20then%20the%20data%20goes%20to%20the%20%22Diluted%22%20tab%20instead.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%3A%20Unknown%20and%20Quality%20Control%20samples%20with%20Dilution%20Factor%20%3D%201%20go%20to%20%22Undiluted%20Tab%22%20and%20if%20they%20are%20%26gt%3B1%20go%20to%20the%20%22Diluted%20Tab%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20its%20not%20guaranteed%20that%20the%26nbsp%3B%22Unknown%22%20or%20%22Quality%20Control%22%20columns%20will%20be%20in%20the%20same%20location%20with%20every%20data%20file%20that%20I%20work%20with.....so%20there%20will%20have%20to%20be%20a%20line%20that%20finds%20the%20column%20on%20its%20own%20each%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BAlso%2C%20it'd%20be%20nice%20if%20the%20headers%20would%20move%20to%20the%20undiluted%20and%20diluted%20sheets%20as%20well%20%3B)%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786084%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788982%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20rows%20from%20one%20sheet%20to%20another%20under%20certain%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Greg%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20consider%20Power%20Query%20as%20an%20options%20to%20select%20raw%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have a spreadsheet can do this with excel formulas, but its really, really clunky. I imagine that VBA can do this easily, but I have no idea how to do VBA. 

What I need:

Move a row of data from the "Raw Data" sheet to the "Undiluted" sheet if:

---the "Sample Type" column says "Unknown" or "Quality Control"

AND

......the "Dilution Factor" column says 1. 

also:

if the dilution factor column says anything other than 1 (i.e. >1) then the data goes to the "Diluted" tab instead. 

 

Basically: Unknown and Quality Control samples with Dilution Factor = 1 go to "Undiluted Tab" and if they are >1 go to the "Diluted Tab"

 

Also, its not guaranteed that the "Unknown" or "Quality Control" columns will be in the same location with every data file that I work with.....so there will have to be a line that finds the column on its own each time.

 

[Also, it'd be nice if the headers would move to the undiluted and diluted sheets as well ]

 

Thanks,


Greg

 

1 Reply
Highlighted

@gms4b 

Hi Greg,

 

Do you consider Power Query as an options to select raw data?