Specified search function that selects all data in that row

[novious1]

Hi guys

Let me preface this by saying I'm new to Vba and there's been a learning curve so please pardon my ignorance. I've been looking through the forums and I haven't seen a problem like mine, but please feel free to let me know if the solution to my question exists out there.

The problem:
So I have an experiment I've been running and our instruments outputs the data into excel. What I would like to do is that I want to take this data that comes from a separate excel sheet and place it into a new excel sheet and have each row of data that contains the particular genotype I am looking at separated out into different columns that can be analyzed. So I was thinking about a message box that was allow you to insert the the number of genotypes, because it can vary per experiment, and a search function that can look for key words such as "wildtype" and take that data and have it inserted into a specific column, ect. The search would also have to be not specific as capitalization can vary.

The attempt:
So I was able to figure out the importation of the data from another sheet but I am lost how to do the search box as described above.
heres what I have so far:

Please Login or Register  to view this content.

^^^
this is just for the importation of the data.

I really appreciate all the help you guys can give on this.
Also as an aside if you could explain how any of the code works I would appreciate it as i find it helpful to learn
Thank you guys again!

[protonLeah]

code tags applied

[novious1]

My apologies. Thank you for your correction.

[Alf]

If I understands your problem propperly you wish to open a number of excel files in a folder and extract data from each file to the master file.

Then you wish to filter the imported data based on several criteria.

Perhaps something like this? You can use a message box for adding the sPath instead of "hard coding". To activate the target file use either filename or "ThisWorkbook" as VBa recognise the workbook with the macro as "ThisWorkbbok". Opening the sorce WB you can activate a specific sheet or if the file only contains one sheet copy the "used" range i.e. "ActiveSheet.UsedRange.Copy", or a specific range.

Please Login or Register  to view this content.

For filering the results I think the "autofilter" funktion would probably be the best. For several criteria in the same column you could use / build an array that you use for filtering.

Alf

[novious1]

Yes that is exactly correct Alf. Would you mind explaining what you mean by

For filering the results I think the "autofilter" funktion would probably be the best. For several criteria in the same column you could use / build an array that you use for filtering.

So to further explain my problem. I get my data exported as in the picture below.
Attachment 384788
and I am tying to create a VBA macro to make:
Attachment 384794
So essentially ill just want everything in the row selected for the genotype, and I want all that data exported into a new master worksheet into columns for each genotype I test. The tricky thing is that the genotypes can vary from 3 like in the example to 4 or 5 depending on the experiment. So as I said previously I was thinking that a message box asking for the number of genotypes with a search tool to select only the genotypes I want for that particular column. But now it occurs to me that just a box asking for the number of genotypes may just be necessary as the data is out putted in regular intervals of 10. Im not sure if im making myself clear.

[Alf]

Sorry could not see attachment, got message "file does not excists".

Re autofilter with array one could uses sometthing like this i.e. from A column (Field:=1) show only these 3 values.

Please Login or Register  to view this content.

I've uploaded a small file with data and a sheet where you add the criteria you wish to filter for.

Credit for transforming column values to row values and setting it up in proper array format goes to forum member apo. He also modified the original macro
so it now acceps both values and strings.

I've siplified this macro a bit.

Alf

[novious1]

Thank you so much Alf this may work for me!!
In case you were curious Ill re upload the pictures

So to further explain my problem. I get my data exported as in the picture below.
Excelpicture1.jpg

[novious1]

and I am tying to create a VBA macro to make:
Excelpicture4.jpg

[Alf]

I'm sorry to say that I'm still confused but on a much higher level than before!

As far I as I understands you are at the moment starting to explore the possibilities of using autofilter with arry so I'll leave you to it and if you have any further questions just post and I'll do my best to help you.

The command

Please Login or Register  to view this content.

will only work on a coherent block of data, so looking at your uploads if you wish to filter the block that starts at row 39 you need to set autofilter for the range A39:W39 and this will automatically extend down to row 58.

Setting for "CurrentRegion" is the "Top Left" cell for that range. For selecting everything on a sheet "ActiveSheet.UsedRange.Select"

Alf

[novious1]

After trying your macro, I was sadly unable to get it to work for me. Sorry that I keep making things more confusing haha. Heres the Vba code I have so far

Please Login or Register  to view this content.

I didn't finish it, but this continues on. I was hoping that there was a more efficient way to write this as I may have to work with more genotypes so I would have to rewrite the code each time that happens. Thank you so much thus far Alf I really appreciate all your help on this.

[Alf]

As I'm still not sure of what values you wish to extract from the files could you perhaps upload a file and show what values you need then I could look at possible means of filtering to get the data you wish for.

So late evening here in Stockholm time to go to bed.

Alf

[novious1]

Hi Alf,

so I attached the raw data that gets exported by our computers and on sheet 2 is how I would like to see it organized. As I get this data from several different computers what I am trying to do is have it all organized into separate columns by the genotype, the word highlighted in yellow. That way it is easier to run statics and calculate the totals for each individually. The trouble is, is that I can have more than 3 of these genotypes, highlighted regions. For example I may have 4 genotypes, thus I would need the data separated into 4 columns so I need a macro that can take that into account.
I didnt organize all the data in the attachment on sheet 2 I was too tired to organize all of the it been a long day.
Thanks for the help thus far Alf!



[ATTACH]385270[/ATTACH)

[Alf]

This is the first model for extracting values. As I’m a bit unsure of how to proceed this model will only handle one file.

To test put the uploaded “example date.xlsx” in a specific folder and run macro “ImportFile” after you have changed the hard coded values for sPath and sFile.

At the moment the macro imports all data from the xlsx file to sheet1 of the “Geno_master”

It starts by finding all the unique values for genotype in the imported data, sets up an autofilter and loops through all the unique values and copies the filtered results to sheet2.

After having organized the data my question is what to do next?

At the moment the imported workbook is closed without any changes. One possibility is to make a copy of sheet2 and add it to the original workbook.

Or clear sheet1 in the “Geno_master” import the next xlsx file in the folder and insert a new sheet in “Geno_master” and copy those new values to that sheet.

Then there must be a naming convention, perhaps new added sheet should have the same name as the imported file?

Well do a bit of testing and see if this model gives you the result you need and consider how I should proceed.

Alf

[novious1]

after you have changed the hard coded values for sPath and sFile.

I'm not sure what you mean by this. Are you talking about the specific location the data is located in on the computer ie)C:\Users\Home\Desktop

[novious1]

I guess what im asking is what do I put for sPath= and sFile=. Because when I run the location of the file that i found by looking at the properties the file with the macro you sent is still empty.
Heres what I did I left clicked on the example data. Then I clicked on the properties. Next i went to the security tab and copied the Object name and place that between the "". Am I doing something. I Apologizes for so many questions.
this is what Ive put in for sPath= and sFile=

Please Login or Register  to view this content.

[Alf]

I Apologizes for so many questions.

No problem, how do we learn if we don't ask?

The sPath is the "description" of how to "find" the folder where your files are stored.

To test the macro I put your uploaded file in the "a_tamp" folder on my "D" drive so the sPath is then

Please Login or Register  to view this content.

and the files that should be tested are all the ".xlsx" files in that folder so you add the "wild card" "*" in order to get all files with the ".xlsx" extension so sFile becomes

Please Login or Register  to view this content.

So if you put the test file (remember at the moment macro only work for 1 file) in "User\Lab\Desktop" folder on the "C" drive your sPath is then

Please Login or Register  to view this content.

And the last "\" is most important as the macro will not work if it's missing!

The sFile to find the file is then

Please Login or Register  to view this content.

Hope this makes things a bit more easy to understand.

Alf

[novious1]

Alf

This is exactly what I was looking for. This is the way I need the data organized. Thank you for your patience with me!!. I got it to work after your great explanation. Now the last things I need is to use this to import multiple files exactly like that example data I had shown you. And finally for I need to be able to use it for multiple genotypes. Because in this case we only had 3, but there can be more or fewer.Thank you so much for your help thus far Alf!

[novious1]

As an aside you don't have to explain, but for learning purposes.

Please Login or Register  to view this content.

Can you explain why do you do Heading on sheet 1, ie) Heading 1, Heading 2
Is this a way of filtering the genotypes? I tried to f8 through your macro but I still don't understand how that works

[Alf]

Ok, here is the “sharp” version that can handle all files in a folder, well almost as each file data is written to a new sheet in master file and there is a limit to the number of sheets in a workbook (a function limited by available memory).

As before you must change sPath and sFile to suit your needs. The last part in sFile “*.xlsx” ensures that all files with xlsx extension are being opened and data extracted.

“ActiveSheet.UsedRange.Offset(4).Copy” copies all data from the opened file minus the 4 first rows and pastes it to sheet1 of “ Geno_master”. As the macro is run from “Geno_master” excel will know that when I “ask” fore something to be done with “ThisWorkbook” excel understands I mean “Geno_master”.

Macro the extracts the file name – “.xlsx” part so if file name was “Lab_test10.xlsx” the string sSht now contains the value “Lab_test10”

Then the file “Lab_test10.xlsx” is closed and no changes are saved i.e. your original data is intact.

Since I’m working with autofilter I find it easier when having a header above my data so I set a string of values along row 9. The Cells command take two
parameters, the first is the row number and the second one is the column number and as the column number loops from 1 to 11 I get headings in row 9 column A to K.

Since filtering is to be done based on genotypes macro must extract all unique values in column C starting at row 10 and going down to the last row in the
C range. So if there is 1 or a 100 different genotypes the part that starts with “For Each cell In Range("C10:C" & Range("C" & Rows.Count).End(xlUp).Row)” will
find them all.

The first unique value is written to M2, the next to M3 and so fort until all values in C column checked and unique values added to the M column. This range in the M column with all the unique genotypes found in the file is named “unique”

Now I need a new sheet to collect the filtered data so macro adds a new sheet after sheet1 and the sheet names is “sSht” i.e. file name for the last opened
file - .xlsx part so it should be “Lab_test10” in accordance with what I said above.

We need to go back to Sheet1 (Geno_master) and run autofilter.

Macro starts by defining a “For Loop” i.e. “For Each cell In Range("unique")”
Sets autofilter on data, define criteria column (Field:=3) i.e. column C and define the criteria as "cell.Value" from the of list with unique genotypes.

The filtered range is copied (Offset(1) i.e. no headings included and pasted to the new sheet (Lab_test10”). Where to? Well to Cells(1, i) and as 1 is the row
and i is the column, i = 1 coped values will be pasted to cell A1.

To prepare for the next genotype i is adjusted “i = i + 12” because each block of data spans 11 columns and there should be 1 empty column between on block and the next.

Macro jumps up to autofilter settings, cell.Value is now the next cell in range “unique” and autofiltered values will change accordingly.
Copies the new filtered range and pastes it to Cells(1, i) but now i is 13 so filtered result is pasted to cell M1 and so it loops for the rest of the values in
range unique and i is adjust accordingly so values are pasted in right position.

Finally columns “A:N” is deleted the next file is opened, data extracted ……….

A macro is a verry nice and labour saving thing but it requires a repetive pattern. Here I assume that all genotypes is found in column C in all the files and that every file has 4 rows with comments so that data starts on row 5. Change any of those "prameters in a file" and the macro will fall flat on it's feet as far as
macros have feets.

Phuuuuuuuh!

Alf

[novious1]

Thank you for the Great explanation! This clears up a lot of the questions I had.

Ok, here is the “sharp” version that can handle all files in a folder, well almost as each file data is written to a new sheet in master file and there is a limit to the number of sheets in a workbook (a function limited by available memory).

So I put in all the data excel files I had for my experiment in folder called Data extraction on my desktop and I changed the sPath and sFile accordingly. But unfortunately when I click run only sheet 1 gets data placed into it and its only from one excel file. was I supposed to add the new code from the Geno_mast file to the old one?

[Alf]

The new uploaded file have the updated macro for looping through all files in the specified folder set by the sPath value. It will look for all files that is of type "xlsx" as set by the dir
sPath command "sFile = Dir("D:\a_tamp\*.xlsx")

So you only need to change the sPath and sFile in the last uploaded file.

If you only get the result from 1 file I would suggest you check the folder where the file are and see how many they are and if they really have ".xlsx" exstention. If the exstention is ".xls" change the ".xlsx" in the sFile command to "*.xls"

You will also need to change

Please Login or Register  to view this content.

as the string ".xls" is only 4 characters long as uposed to ".xlsx" i.e. 5

You could also record am macro opening one file from this particular folder to see the you have the propper string for sPath.

Alf

[novious1]

I ended up converting the all the files to .xlsx, but I'm still getting the same problem when I run the macro. The macro doesn't sort the data either as in the previous macro you had sent it. Only seems to import the data. I was running through it step-wise and it seems to be copying and pasting over itself

[Alf]

I see you profile says Ms Office 11 are you running this macro in an Mac environment?

If so then I don't know if it will work. This macro is written and tested in Office 2010 where it works without any problem but Mac ???.

Alf

[novious1]

No, Im running windows 7 currently. Maybe my download was somehow corrupted. This is the the macro I received along with the file pathway Im extracting the data from.

Please Login or Register  to view this content.

[Alf]

Since you are running Windows 7 the only other thing that can cause a problem could be your office version. I rencently got feedback from an OP where the solution I suggested worked ok in Office 2010 but when he tested it under Office 2013 did not work.

See posts #10, #11 and #12 in link

http://www.excelforum.com/excel-prog...her-sheet.html

Based you you recording macro should look like this then:

Please Login or Register  to view this content.

Hope this works for you if not I really don't know what to suggest.

Alf

Ps Keeping fingers crossed.

[novious1]

It works excellently!!!
Last thing I have to ask, I know Im sorry, but is it possible instead of having the data in there own sheet. That they could be pasted beneath each other in just one sheet. Like after the last active cell. I was trying to record a macro to do this but i realized that the number of sheets can vary, and i don't know what to write to take that into account.

[Alf]

It works excellently!!!

Glory to be! So what was the problem really? Good for me to know when I try to do "trouble shooting" long distance.

data in there own sheet. That they could be pasted beneath each other in just one sheet

Yes absolutely possible but I must know a bit more how it should be presented.

At the moment all genotypes are placed in different blocks. When the next file is opened do wish the next set of genotype blocks be placed in the same sheet but a directly beneath the previous blocks or having a blank row between? Should the file name be placed somewhere in order to identify different set of genotype blocks?

As the number of genotypes may vary with each file blocks above and beneath may not have same genotype as the one in between if all file data is placed beneath eachother.

At the moment all files are first imported to the master file, the file is closed and import sorted in different blocks. Perhaps all files should first be imported to master file and then treated to be sorted into the proper number of genotype blocks on a data sheet?

Think about it and post back when you know how result should be presentet. A small file with data sorted in the proper order to show would also be of help.

Alf

[novious1]

Well the problem I believe was when I downloaded the new Geno_master from post #19 it was missing

Please Login or Register  to view this content.

it only contained the code I had posted in post #24

Not sure if thats what made the difference or not but it works for me now.

As for the data the current way your having it organized is perfect. What I need is to display the file name that it came from on the side of the most right column next to the data that it corresponds to. The data should just be pasted beneath each other with no spaces. The attachment will hopefully explain this better.Layout Example.xlsx

[Alf]

Well then it seems that the problem was caused by me when I uploaded the wrong file. One should never underestimate one own's stupidity when things go wrong!

Having tested and as far as I can see macro works ok, but you better do some testing yourself to see if it produces "reliable" results.

Have added a "Data" sheet in the macro so you will have to do same as the sheet name is hard coded as Sheets("Data").

The main problem was sorting out the last row used in the "Data" sheet as I assumed that it was not sure that every file had an equal number of rows for each genotype found in the file.

Since the pasting is done to the last row used + 1 to ensure that new data is not pasted over exciting data the macro needs to find the last row used.

I've also added a bit of "house cleaning" i.e. "Data" sheet is cleaned of excisting data at the start of a new run.

Please Login or Register  to view this content.

Alf

[novious1]

Hi Alf,

Its so close to being perfect just a slight problem when its importing data from the 1st work sheet it misses the first minute for the Canton-s genotype so that causes that column to be off by 1 resulting in gaps ill upload the picture to illustrate the problem. So Close!!!
Thanks for all you work thus far !
data sheet problem.jpgdata sheet problem2.jpg

[novious1]

The highlighted areas are just to compare the two different trial. If you notice trial 7 is missing Start-1:00 in comparison to trial 10. and it causes the spaces that you see in line 170 in the picture.

[Alf]

Yes I see so change line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

that should solve this problem.

Alf

[novious1]

Alf you truly are the Greatest!!!!!!!!!!!!!

You've really helped me to reduce the monotonous work I had to do to analyze my data.

Thank you so much!!!!

[Alf]

Glad to be of help and thanks for feed back.

But remember this macro is buld to order i.e. 4 rows of comments and genotypes are found in column C. If any of these parameters are changed / different in a file result will be screw up.

If this solves your problem don't forget to mark thead "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved