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]

Peter, thanks so much- please see answers below
if you cant do it, i'm very happy to receive an absolute NO sorry go away!

Chloe

In relation to my code ..
In what way did it fail?
- Error message? If so, details please. it says:Compile error invalid outside procedure
- It did nothing?
- It copied data to the Summary sheet but it was the wrong data? If so, details please.
- Something else? If so, details please.


Are the source columns still columns C, E & I on each of the sheets 1-4?

no they are different on each sheet please see below in Red
A: Subject ID
sheets
1:C
2:C
3:D
4:D
B: CDMCPE *this is the chemo cycle*
1:E
2:I
3:F
4:J
C: Sample type
1:G
2:E
3:K
4:I
D: Sheet name

 

[Peter_SSs]

if you cant do it, i'm very happy to receive an absolute NO sorry go away!

This is definitely doable! Just have to get to know exactly what you have, what can change, what can go wrong etc.

Rather than hard-code the different columns, this attempt actually locates the headings wherever they are (in row 1) on each sheet. It does, therefore, rely on their spelling (which is taken from the Summary sheet A1:C1) being exactly the same in each sheet. If that may not be the case, then we may have to resort to hard-coding the columns (or ensure the spellings are the same in future).

Give it a try.

Sub Combine()
  Dim myHeaders, aRws, aCols(1 To 3) As Long
  Dim ws As Worksheet, wsSumm As Worksheet
  Dim nr As Long, LR As Long, i As Long
  
  Set wsSumm = Sheets("Summary")
  wsSumm.UsedRange.Offset(1).ClearContents
  myHeaders = wsSumm.Range("A1:C1").Value
  nr = 2
  For Each ws In Worksheets
    If ws.Name <> wsSumm.Name Then
      With ws
        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) = ws.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
    End If
  Next ws
End Sub

 

[chloev]

Oh my God, it worked.

you are the cleverest person !!

thank you so much!!

Chloe

This is definitely doable! Just have to get to know exactly what you have, what can change, what can go wrong etc.

Rather than hard-code the different columns, this attempt actually locates the headings wherever they are (in row 1) on each sheet. It does, therefore, rely on their spelling (which is taken from the Summary sheet A1:C1) being exactly the same in each sheet. If that may not be the case, then we may have to resort to hard-coding the columns (or ensure the spellings are the same in future).

Give it a try.

Sub Combine()
  Dim myHeaders, aRws, aCols(1 To 3) As Long
  Dim ws As Worksheet, wsSumm As Worksheet
  Dim nr As Long, LR As Long, i As Long
  
  Set wsSumm = Sheets("Summary")
  wsSumm.UsedRange.Offset(1).ClearContents
  myHeaders = wsSumm.Range("A1:C1").Value
  nr = 2
  For Each ws In Worksheets
    If ws.Name <> wsSumm.Name Then
      With ws
        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) = ws.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
    End If
  Next ws
End Sub

 

[Peter_SSs]

Oh my God, it worked.

you are the cleverest person !!

thank you so much!!

Chloe

You are very welcome. Thanks for the update.

 

[chloev]

oh no - i have just released, it hasn't 100% worked.
For some reason the data from the "resulted" tab didn't pull through correctly- it hasn't pulled through the subject ID-it has pulled through info from the column before that called "site"
Also the CDM CPE column it seems to have pulled through data from a column called Alias subject ID? do you have any idea why this might be ?
it may be because the resulted tab has a lot more columns in it?

Chloe

You are very welcome. Thanks for the update.

 

[Peter_SSs]

do you have any idea why this might be ?

Yes, the first thing I would check very carefully is..

It does, therefore, rely on their spelling (which is taken from the Summary sheet A1:C1) being exactly the same in each sheet.

 

[chloev]

yes, i promise they're identical.
i checked this thoroughly because i was going to change them to be exactly the same if not. i've just checked again and they are identical.
Chloe

Yes, the first thing I would check very carefully is..

 

[Peter_SSs]

yes, i promise they're identical.

Let's investigate a little further.
Put this code into your workbook and run it. It will add a new worksheet and in that new worksheet, list all the other worksheets (excluding the Summary) and what column in each worksheet each heading is found.
See if that turns up anything unusual.
The extra sheet can then be deleted.

Sub Checking()
  Dim ws As Worksheet, wsSumm  As Worksheet
  Dim results
  Dim i As Long, k As Long
  
  ReDim results(1 To Sheets.Count, 1 To 4)
  
  Set wsSumm = Sheets("Summary")
  results(1, 1) = "Sheet"
  results(1, 2) = wsSumm.Range("A1").Value
  results(1, 3) = wsSumm.Range("B1").Value
  results(1, 4) = wsSumm.Range("C1").Value
  k = 1
  For Each ws In Worksheets
    If ws.Name <> wsSumm.Name Then
      k = k + 1
      results(k, 1) = ws.Name
      For i = 1 To 3
        On Error Resume Next
        results(k, i + 1) = ws.Rows(1).Find(What:=results(1, i + 1), LookAt:=xlWhole).Column
        On Error GoTo 0
      Next i
    End If
  Next ws
  Sheets.Add
  ActiveSheet.Range("A1").Resize(UBound(results), 4).Value = results
End Sub

 

[chloev]

Hi,
I'm sorry I disappeared, the day after this message I found out I am being let go in August (amongst many others (nightmare) ) so I lost motivation on this task, but it has returned.
I have now got over it and I’m coming out of hiding,so I would really appreciate it if would continue to help me, Peter?

Ok so I tried your code and interestingly the new sheet showed the columns in each sheet for all headings but for the “sample type” heading it was just blank?
[TABLE="width: 400"]
<tbody>[TR]
[TD]Sheet
[/TD]
[TD]Subject ID
[/TD]
[TD]CDM CPE
[/TD]
[TD]Sample type
[/TD]
[/TR]
[TR]
[TD] Pending Analysis Viracore
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BSM Discrepancy
[/TD]
[TD]3
[/TD]
[TD]9
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BSM awaiting shipment
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Resulted
[/TD]
[TD]4
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Graphs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any ideas?
So sorry I hope you have the time to remind yourself of this problem.

Thanks so much
Chloe

Let's investigate a little further.
Put this code into your workbook and run it. It will add a new worksheet and in that new worksheet, list all the other worksheets (excluding the Summary) and what column in each worksheet each heading is found.
See if that turns up anything unusual.
The extra sheet can then be deleted.

Sub Checking()
  Dim ws As Worksheet, wsSumm  As Worksheet
  Dim results
  Dim i As Long, k As Long
  
  ReDim results(1 To Sheets.Count, 1 To 4)
  
  Set wsSumm = Sheets("Summary")
  results(1, 1) = "Sheet"
  results(1, 2) = wsSumm.Range("A1").Value
  results(1, 3) = wsSumm.Range("B1").Value
  results(1, 4) = wsSumm.Range("C1").Value
  k = 1
  For Each ws In Worksheets
    If ws.Name <> wsSumm.Name Then
      k = k + 1
      results(k, 1) = ws.Name
      For i = 1 To 3
        On Error Resume Next
        results(k, i + 1) = ws.Rows(1).Find(What:=results(1, i + 1), LookAt:=xlWhole).Column
        On Error GoTo 0
      Next i
    End If
  Next ws
  Sheets.Add
  ActiveSheet.Range("A1").Resize(UBound(results), 4).Value = results
End Sub

 

[Peter_SSs]

I'm sorry I disappeared, the day after this message I found out I am being let go in August ..

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.

the new sheet showed the columns in each sheet for all headings but for the “sample type” heading it was just blank?

Any ideas?

Yes, the following was not correct.

yes, i promise they're identical.
i checked this thoroughly because i was going to change them to be exactly the same if not. i've just checked again and they are identical.
Chloe

Check again, especially for leading trailing spaces in both the Summary and the other sheets.

If you still believe they are identical, then on the Summary sheet, in a vacant cell put this formula:
=CODE(MID(C1,7,1))
changing the C1 if needed to point at the "Sample type" heading.
Do the same thing on one of the other sheets.
Report the results