extract data from 3 sheets into one table with 3 variables to display sheet name ?

[chloev]

Hello,
First post and i'm a scientist, with no programming experience so by all means tell me if i am being unreasonable with my expectations or if i am being incredibly stupid !
I have a workbook containing with 3 reports
Each report represents a status of a type of blood sample (1 per row), so one sample type (3 column variables) will only ever appear on 1 of the 3 sheets.

I want to create a large table that pulls columns C, E & I from each row with a fourth column telling me which sheet is has pulled from- so essentially a big list of 3 columns that are common to all reports, plus am additional column which tells me which report it has come from

This will give me the status of each sample and then I should be able to get summary data from this large table.
Does that make sense? And is it possible?
I have looked through lots of the amazing you tube videos related to this site but can’t seem to find anything that would work.
Another important thing is that the reports will be updated monthly with more rows added.
I would appreciate any help and even just to point me in the direction of what to put into youtube search!!
Thanks so much
From an amateur!!
i don't know if you can see my screen shots?

 

[chloev]

That isn't good news.
However, sometimes good things come from such adversities in the end. Let's hope that is the case for you.

yes i agree

ive checked again and realised they were different. on the summary the spelling was "Sample type" and on the other sheets is was "Sample Type" !!
i have changed the summary sheet to match the other sheets and re entered the code but now im getting an error code "run time error 91, object variable or with block variable not set". when i click debug it highlights the following row of the code aCols(i) = ws.Rows(1).Find(What:=myHeaders(1, i), LookAt:=xlWhole).Column

Do you have any idea why ?

Chloe

 

[Peter_SSs]

Do you have any idea why ?

Yes. The results you gave in post #19, indicated a sheet 'Graphs' that did not have any of the columns headings.
Previously you had indicated that the workbook contained 5 sheets including the Summary sheet. Is 'Graphs' a 6th sheet that should be excluded from the consolidation to the summary?


If so, change this line:

If ws.Name <> wsSumm.Name And ws.Name <> "Graphs" Then

 

[chloev]

Yes. The results you gave in post #19, indicated a sheet 'Graphs' that did not have any of the columns headings.
Previously you had indicated that the workbook contained 5 sheets including the Summary sheet. Is 'Graphs' a 6th sheet that should be excluded from the consolidation to the summary?


If so, change this line:

If ws.Name <> wsSumm.Name And ws.Name <> "Graphs" Then

I tried adding this and got the same error.
I then removed the graph sheet, I have removed all macros and started again and its still bringing up the same error. The break point is now the top line since I have messed around with it...

 

[Peter_SSs]

As a further test:

1. Leave the graphs sheet out for the moment.
2. Copy cell A1 of the Summary sheet and Paste over wherever this heading is in row 1 in each of the other 4 sheets.
3. Repeat step 2 for cell B1 and cell C1 of the Summary sheet.

Re-copy the code from post #12 and run it again.
What happens?

 

[chloev]

As a further test:

1. Leave the graphs sheet out for the moment.
2. Copy cell A1 of the Summary sheet and Paste over wherever this heading is in row 1 in each of the other 4 sheets.
3. Repeat step 2 for cell B1 and cell C1 of the Summary sheet.

Re-copy the code from post #12 and run it again.
What happens?

YAAAAS its worked ray:

Couple of other questions, first one is unsurprising.
1. How do I add new sheets without affecting the summary sheet ?
2. Column B of the summary sheet- each row (apart from row 1) contains a number (example 32416002002), can i create a new column in the summary sheet which pulls the middle 5 digits out of the data in column B for each row?
3. Where can i learn how to do this myself, any courses you could recommend?

Thanks so much
Chloe

 

[Peter_SSs]

YAAAAS its worked ray:

That means that the headings you kept saying were identical were not identical, even after correcting the T in "Sample Type".

If you are going to be doing this task repeatedly, you need to identify what that issue is. The last part of post #20 may point you in the right direction. Otherwise, see the first part of post #6 again and post small screen shots of the actual row 1 from each of the 4 sheets and the Summary sheet so I can take a look.

1. How do I add new sheets without affecting the summary sheet ?

We need to be able to identify either ...
the name of the sheets to be included in the Summary, or
the name of the sheets to be excluded from the Summary, or
the position of the sheets to be included in the Summary (eg they are always the left 4 sheets), or
the position of the sheets to be excluded from the Summary (eg they are always the right 3 sheets), or
something that is in each of these 4 sheets that is not in the sheets to be excluded (or vice-versa), or
some other way.

2. Column B of the summary sheet- each row (apart from row 1) contains a number (example 32416002002), can i create a new column in the summary sheet which pulls the middle 5 digits out of the data in column B for each row?

That will be easy & will be included once the above is resolved.

3. Where can i learn how to do this myself, any courses you could recommend?

I learned virtually all I know from this forum, the built-in Help & Google. It is why I joined the forum and spent time here to begin with. I have not done any courses.

 

[chloev]

That means that the headings you kept saying were identical were not identical, even after correcting the T in "Sample Type".

If you are going to be doing this task repeatedly, you need to identify what that issue is. The last part of post #20 may point you in the right direction. Otherwise, see the first part of post #6 again and post small screen shots of the actual row 1 from each of the 4 sheets and the Summary sheet so I can take a look.

i cant get the add in to work. I successfully have it into excel but for some reason cant use it, i think its my work computer. I do have admin rights but for some reason its just not working (spent an hour yesterday trying )

i think thought, the issue should be resolved now that i have the correct spellings in the summary page because this will never change and neither will the 1st rows of the input sheets? do you agree, or am i missing something?

We need to be able to identify either ...
the name of the sheets to be included in the Summary, or
the name of the sheets to be excluded from the Summary, or
the position of the sheets to be included in the Summary (eg they are always the left 4 sheets), or this one would work! they are always the left 4 sheets in the in the workbook
the position of the sheets to be excluded from the Summary (eg they are always the right 3 sheets), or
something that is in each of these 4 sheets that is not in the sheets to be excluded (or vice-versa), or
some other way.


That will be easy & will be included once the above is resolved.

great let me know if you think its OK based on what i have said above.

I learned virtually all I know from this forum, the built-in Help & Google. It is why I joined the forum and spent time here to begin with. I have not done any courses.

this is very reassuring, im going to make it my mission to learn this language!

Thanks a lot,
Chloe

 

[Peter_SSs]

Hard to find your responses amongst my quote. Need to be outside quote or all highlighted like the red bit.

Which Add-In did you try?
What are the symptoms that make you say it is not working?
Is your Excel 32-bit or 64-bit?

OK, if the input sheets row 1 and Summary row 1 never get updated, then they should continue to work. I had thought that the input sheets might have been output from some other program or excel process that may have re-introduced rogue characters.

If the input sheets are always the left 4 sheets then you can add as many sheets as you like to the right of that & call them whatever you want, so long as one of them is 'Summary' and use this code.
It includes extract the 5 digits from column B.

Sub Combine_Left4()
  Dim myHeaders, aRws, aCols(1 To 3) As Long
  Dim wsSumm As Worksheet
  Dim nr As Long, LR As Long, i As Long, sh As Long
  
  Set wsSumm = Sheets("Summary")
  wsSumm.UsedRange.Offset(1).ClearContents
  myHeaders = wsSumm.Range("A1:C1").Value
  nr = 2
  For sh = 1 To 4
    With Sheets(sh)
      LR = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious, SearchFormat:=False).Row
      aRws = Evaluate("row(2:" & LR & ")")
      For i = 1 To 3
        aCols(i) = .Rows(1).Find(What:=myHeaders(1, i), LookAt:=xlWhole).Column
      Next i
      wsSumm.Cells(nr, 1).Resize(UBound(aRws), 3).Value = Application.Index(.Cells, aRws, aCols)
      wsSumm.Cells(nr, 4).Resize(UBound(aRws)).Value = .Name
      nr = nr + UBound(aRws)
    End With
  Next sh
  With wsSumm
    .Range("E1").Value = "5 digits"
    With .Range("E2:E" & .Range("D" & .Rows.Count).End(xlUp).Row)
      .Formula = "=Mid(B2,5,5)"
      .Value = .Value
    End With
  End With
End Sub

 

[chloev]

Hard to find your responses amongst my quote. Need to be outside quote or all highlighted like the red bit.

Which Add-In did you try?
What are the symptoms that make you say it is not working?
Is your Excel 32-bit or 64-bit?

OK, if the input sheets row 1 and Summary row 1 never get updated, then they should continue to work. I had thought that the input sheets might have been output from some other program or excel process that may have re-introduced rogue characters.

If the input sheets are always the left 4 sheets then you can add as many sheets as you like to the right of that & call them whatever you want, so long as one of them is 'Summary' and use this code.
It includes extract the 5 digits from column B.

Sub Combine_Left4()
  Dim myHeaders, aRws, aCols(1 To 3) As Long
  Dim wsSumm As Worksheet
  Dim nr As Long, LR As Long, i As Long, sh As Long
  
  Set wsSumm = Sheets("Summary")
  wsSumm.UsedRange.Offset(1).ClearContents
  myHeaders = wsSumm.Range("A1:C1").Value
  nr = 2
  For sh = 1 To 4
    With Sheets(sh)
      LR = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious, SearchFormat:=False).Row
      aRws = Evaluate("row(2:" & LR & ")")
      For i = 1 To 3
        aCols(i) = .Rows(1).Find(What:=myHeaders(1, i), LookAt:=xlWhole).Column
      Next i
      wsSumm.Cells(nr, 1).Resize(UBound(aRws), 3).Value = Application.Index(.Cells, aRws, aCols)
      wsSumm.Cells(nr, 4).Resize(UBound(aRws)).Value = .Name
      nr = nr + UBound(aRws)
    End With
  Next sh
  With wsSumm
    .Range("E1").Value = "5 digits"
    With .Range("E2:E" & .Range("D" & .Rows.Count).End(xlUp).Row)
      .Formula = "=Mid(B2,5,5)"
      .Value = .Value
    End With
  End With
End Sub

YES its perfect! thanks so much!
I'm now going to embark on learning!
I used Excel Genie. i couldn't get it to appear on the menu. i'm going to re-install today and see if it works.
Thanks so much.ray:ray:ray:
Chloe

 

[Peter_SSs]

I used Excel Genie. i couldn't get it to appear on the menu.

It should appear in the Add-Ins tab on the ribbon menu.