Specified search function that selects all data in that row

My apologies. Thank you for your correction.

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.

Option Explicit
Sub ProcessAllFiles()

Dim sPath As String
Dim sFile As String
Dim Wb As Workbook
Dim i As Long

        sPath = "C:\Documents\"
        sFile = Dir(sPath & "*.xl*")
        
        Application.ScreenUpdating = False
        
    Do While sFile <> ""
            Set Wb = Workbooks.Open(sPath & sFile)
            i = Range("C" & Rows.Count).End(xlUp).Row
            Range("C1:C" & i).Copy
            ThisWorkbook.Sheets("Sheets2").Activate
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
            Application.CutCopyMode = False         
           Wb.Close SaveChanges:=False
    sFile = Dir
    Loop
   Application.ScreenUpdating = True
End Sub

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

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.

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.

ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Array("genotyp1", "fenotype1", "genotyp3"),  Operator:=xlFilterValues

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

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

Range("A1").CurrentRegion.AutoFilter

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

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

Sub Computer1()
    'Messagebox
Call MsgBox("Note: Please select data inputs from Ethovision for each computer receptively. All data will be displayed on Sheet2", vbOKOnly, "Welcome")
    Dim SaveDriveDir As String, MyPath As String
    Dim FName As Variant

    SaveDriveDir = CurDir
    MyPath = Application.DefaultFilePath    'or use "C:\Data"
    ChDrive MyPath
    ChDir MyPath
    FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*")

    If FName = False Then
        'do nothing
    Else
    '1st Genotype
    GetData FName, "Analysis", "A5:K14", Sheets("Sheet2").Range("A97"), False, False
    '2nd Genotype
    GetData FName, "Analysis", "A15:K24", Sheets("Sheet2").Range("M97"), False, False
    '3rd Genotype
    GetData FName, "Analysis", "A25:K34", Sheets("Sheet2").Range("Y97"), False, False
    '1st Genotype
    GetData FName, "Analysis", "A35:K44", Sheets("Sheet2").Range("A107"), False, False
    '2nd Genotype
    GetData FName, "Analysis", "A45:K54", Sheets("Sheet2").Range("M107"), False, False
    '3rd Genotype
    GetData FName, "Analysis", "A55:K64", Sheets("Sheet2").Range("Y107"), False, False
    'this will go on etc, etc...

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.

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

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

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

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=

sPath = "C:\Users\Lab\Desktop\Example data"
sFile = Dir("C:\Users\Lab\Desktop\Example data.xlsx")

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

sPath = "D:\a_tamp\"

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

sFile = Dir("D:\a_tamp\*.xlsx")

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

sPath = "C:\User\Lab\Desktop\"

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

The sFile to find the file is then

sFile = Dir("C:\User\Lab\Desktop\*.xlsx")

Hope this makes things a bit more easy to understand.

Alf

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!

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

For i = 1 To 11
Cells(9, i) = "Heading" & " " & i

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

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

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?

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

sSht = Mid(wb.Name, 1, Len(wb.Name) - 4)

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

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

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

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.

Sub ImportFile()
Dim sPath As String
Dim sFile As String
Dim wb As Workbook
Dim i As Integer

sPath = "C:\Users\Lab\Desktop\Data Extraction\"
sFile = Dir("C:\Users\Lab\Desktop\Data Extraction\*.xlsx")
Do While sFile <> ""
 Set wb = Workbooks.Open(sPath & sFile)
 ActiveSheet.UsedRange.Offset(4).Copy
 ThisWorkbook.Activate
 Range("A1").Offset(1, 0).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
 wb.Close SaveChanges:=False
 sFile = Dir
Loop
ActiveSheet.UsedRange.Columns.AutoFit
'Application.DisplayAlerts = True
' Application.ScreenUpdating = True
End Sub

Sub Fixa()
Dim i As Integer

For i = 1 To 11
Cells(1, i) = "Heading" & " " & i
Next
End Sub

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:

Sub ImportFile()
Dim sPath As String
Dim sFile As String
Dim wb As Workbook
Dim sSht As String
Dim i As Integer
Dim cell As Range

Application.ScreenUpdating = False

sPath = "C:\Users\Lab\Desktop\Data Extraction\"
sFile = Dir("C:\Users\Lab\Desktop\Data Extraction\*.xlsx")
Do While sFile <> ""
 Set wb = Workbooks.Open(sPath & sFile)
 ActiveSheet.UsedRange.Offset(4).Copy
 ThisWorkbook.Activate
 Range("A10").PasteSpecial xlPasteValues
 Application.CutCopyMode = False
 
 sSht = Mid(wb.Name, 1, Len(wb.Name) - 5)
 
 wb.Close SaveChanges:=False
 
 For i = 1 To 11
Cells(9, i) = "Heading" & " " & i
Next

i = 0

For Each cell In Range("C10:C" & Range("C" & Rows.Count).End(xlUp).Row)

If Application.CountIf(Columns(13), cell) = 0 Then
    Range("M2").Offset(i, 0) = cell.Value
    i = i + 1
End If

Next

Range("M2:M" & Range("M" & Rows.Count).End(xlUp).Row).Name = "unique"

i = 1

Sheets.Add After:=Sheets("Sheet1")

ActiveSheet.Name = sSht

Sheets("Sheet1").Activate

For Each cell In Range("unique")

ActiveSheet.Range("$A$9:$K$9").AutoFilter Field:=3, Criteria1:=cell.Value

ActiveSheet.AutoFilter.Range.Offset(1).Copy

Sheets(sSht).Cells(1, i).PasteSpecial xlPasteValues

Application.CutCopyMode = False

i = i + 12

Next cell

Columns("A:N").Delete Shift:=xlToLeft

 sFile = Dir
 
Loop

For i = 2 To Sheets.Count

Sheets(i).Activate

ActiveSheet.Columns.AutoFit

Next

Sheets(1).Activate

Application.ScreenUpdating = True

ActiveSheet.AutoFilterMode = False

Range("A1").Select

End Sub

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

Alf

Ps Keeping fingers crossed.

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.

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

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

sSht = Mid(wb.Name, 1, Len(wb.Name) - 5)
 
 wb.Close SaveChanges:=False
 
 For i = 1 To 11
Cells(9, i) = "Heading" & " " & i
Next

i = 0

For Each cell In Range("C10:C" & Range("C" & Rows.Count).End(xlUp).Row)

If Application.CountIf(Columns(13), cell) = 0 Then
    Range("M2").Offset(i, 0) = cell.Value
    i = i + 1
End If

Next

Range("M2:M" & Range("M" & Rows.Count).End(xlUp).Row).Name = "unique"

i = 1

Sheets.Add After:=Sheets("Sheet1")

ActiveSheet.Name = sSht

Sheets("Sheet1").Activate

For Each cell In Range("unique")

ActiveSheet.Range("$A$9:$K$9").AutoFilter Field:=3, Criteria1:=cell.Value

ActiveSheet.AutoFilter.Range.Offset(1).Copy

Sheets(sSht).Cells(1, i).PasteSpecial xlPasteValues

Application.CutCopyMode = False

i = i + 12

Next cell

Columns("A:N").Delete Shift:=xlToLeft

 sFile = Dir
 
Loop

For i = 2 To Sheets.Count

Sheets(i).Activate

ActiveSheet.Columns.AutoFit

Next

Sheets(1).Activate

Application.ScreenUpdating = True

ActiveSheet.AutoFilterMode = False

Range("A1").Select

End Sub

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

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

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.

Yes I see so change line

ActiveSheet.Range("$A$2:$K$2").AutoFilter Field:=3, Criteria1:=cell.Value

to

ActiveSheet.Range("$A$1:$K$1").AutoFilter Field:=3, Criteria1:=cell.Value

that should solve this problem.

Alf

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!!!!

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