searching for data in columns with the same argument

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
17
Hello.
I am trying to copy/paste data from 2 columns (containing several rows with required data) and transfer the columns to another worksheet in the workbook
over a time span of either 23-24-or 25 hrs
The problem is that I need to use an argument what is not consistent and I have a macro in the workbook what adds a column at the end each time I enter new data.

In the attached I have the source sheet, which is here called "report"and the destination sheet, what is called "for technical report"

In row 6 there are mentioned occasions - which are named in the cell as: noon, eosp, sosp, departure, arrival and some others, which are not important
As said, when I update the daily reports - there is a column added at the end. Thus every day and every occasion a column is added in the sheet "reports'.

What I am looking to do is the following:
Copy / Paste the LAST Noon report and the previous (before the LAST) Noon report to a new column in another sheet, so that I have in the new columns the data in the columns of the last - either 23/24/25 hours (depends if we shift time when travelling East or West). If I have that data in another sheet, i can extract the data what I need on a daily basis.
It doesn't have to contain formulae. Just the data is sufficient
I have tried all sorts, both with macros and the build in formulas, but I am getting nowhere.
Hope you can assist
 

Attachments

  • source sheet.png
    source sheet.png
    32.7 KB · Views: 15
  • destination sheet.png
    destination sheet.png
    36.8 KB · Views: 14

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Which code are you using, Post #18 or Post #19? and by first colu7mn I assume you mean Columbn B with the event and item descriptions.

This is Post #18 code mocified to incluse column B. as 1st colmun on sheet2.
VBA Code:
Sub t8()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, x As Long, col As Long
Set sh1 = Sheets("Report")
Set sh2 = Sheets("for technical report")
col = sh1.Cells(6, 1).End(xlToRight).Column + 1
Set fn = sh1.Rows(6).Find("Noon", sh1.Cells(6, col), xlValues, xlWhole, , xlPrevious)
    If Not fn Is Nothing Then
        sh2.Columns(1) = sh1.Columns(2).Value
        x = 3
        Do
            sh2.Columns(x) = sh1.Columns(fn.Column).Value
            Set fn = sh1.Rows(6).FindPrevious(fn)
            x = x - 1
        Loop While x <> 1
    End If
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
In case you were using Post #19 code.

VBA Code:
Sub t9()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, x As Long, col As Long
Set sh1 = Sheets("Report")
Set sh2 = Sheets("for technical report")
Set fn = sh1.Rows(4).Find(Format(Date, "d.m.yyyy"), sh1.Cells(4, Columns.Count), xlValues, xlPart, , xlPrevious)
    If Not fn Is Nothing Then
        col = fn.Offset(, 1).Column
    Else
        MsgBox "Date Not Found, Correction Needed." & vbLf & "Procedure will Terminate!", vbCritical, "NO DATE"
        Exit Sub
    End If
Set fn = Nothing
Set fn = sh1.Rows(6).Find("Noon", sh1.Cells(6, col), xlValues, xlWhole, , xlPrevious)
    If Not fn Is Nothing Then
        sh2.Columns(1) = sh1.Columns(2).Value
        x = 3
        Do
            sh2.Columns(x) = sh1.Columns(fn.Column).Value
            Set fn = sh1.Rows(6).FindPrevious(fn)
            x = x - 1
        Loop While x <> 1
    End If
End Sub
 

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
17
Hi. I am using the one from post #18 - and it works perfectly. Will also check #19 later.
Many thanks for your excellent help. Much appreciated. Makes me also realise I still need to learn a lot :)
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hi. I am using the one from post #18 - and it works perfectly. Will also check #19 later.
Many thanks for your excellent help. Much appreciated. Makes me also realise I still need to learn a lot :)
You're welcome,
Regards, JLG
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,971
Messages
5,767,391
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top