Formulaeto check cell above, if current cell does not meet criteria

L

Liam1

New Member
Joined
Jul 18, 2017
Messages
4
Hello to all,

The document I am working on is generated from an accounts package's job costings module and is a list of costs associated with the jobs that the company has worked (Job Costing) on through out any given period. However, in order to summarise this information the particular job code has to be present next to all its data e.g. The individual material and labour. This has several thousand lines and takes a considerable amount of time to prepare the document.

The job number is what needs to be returned but, this only appears in a column, five places above the header for the labour breakdown section. A basic example of what the data looks like is below.

What I would like to do is insert a new colum left of the material listing and insert a formula that returns the job code (HB 100) below. However, there are hundreds of jobs and each job has a varied amount of rows for materials and labour.

The only constant I can see is that the job number always appears 5 spaces above the material or labour cost header and there is only 3 other repeated text items in the column.

I have tried a formulae that would say, if(or( cell = "" , cell="materials" , cell = "labour" ), select above cell , return selected cell ) which of course just returns the cell above or the cell it was looking at, rather than look at the cell above and then the next until the job number is found.

I apologise if my question is a little hard to follow and will clarify happily if asked specifically.

[TABLE="width: 300"]
<tbody>[TR]
[TD]HB 100[/TD]
[TD]House Build - Joe Bloggs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cladding[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Timber[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cement[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]F Mayweather[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Liam,

I have assumed that the material listing is in Column C. I have provided a macro solution. I'm not sophisticated enough with the type of formula it might take to solve your issue but the macro below worked in the test file I created.
Give it a try and let me know if it works for you.

Code: 
Sub CopyJobID()
Dim lr As Long
Dim CurrRow As Long
Dim JobID As String
Dim i As Long
Dim x As Long
Dim CurrVal As String


lr = Cells(Rows.Count, "C").End(xlUp).Row
Range("C1").EntireColumn.Insert


For i = 1 To lr
    x = i
    Do Until CurrVal = "Material" Or CurrVal = "Labour"
        If x <= lr Then
            CurrVal = Cells(x, 1).Value
        x = x + 1
        Else
            Exit Do
        End If
    Loop
    CurrVal = ""
    
    CurrRow = x - 1
    JobID = Cells(x - 5, 1).Value
    i = x - 1
    Do Until Cells(i, 1).Value <> "" And Cells(i, 1).Value <> "Material" And Cells(i, 1).Value <> "Labour"
        If i <= lr Then
            If Cells(i, 4).Value <> "" Then
                Cells(i, 3).Value = JobID
            End If
        Else
            Exit Do
        End If
        i = i + 1
    Loop
Next


End Sub
 
Upvote 0
Hi Frank_AL,

I appreciate you helping me with this issue and am happy use either a macro or formulae to solve my issue.

I tried using the macro you provided but it seems to just insert a column in column C - is there a particular way in which I need to run this macro?
 
Upvote 0
Liam, the code assumes the Job # and Material /Labour are in Column A. If that's not the case Let me know and I can adjust the code accordingly.
 
Upvote 0
Hello Frank,

You are correct in assuming that the job number material/labour headers are in column A.

I have purely run the macro by creating the format as I shown in the example and clicking run. The macro inserts a new column in column C, but does not produce any formulae in that column. If I am running this incorrectly, please advise.

I am worried that I am not clear and potentially wasting your time with misinformation so have included a table below of exactly what I am seeing when the report is run, with me having manually inserted a new column for the job numbers. As previously stated, this runs for thousands of rows but is consistent with what is shown below.

Column A - These are the only text items found in the column (I was/ am happy to edit macros to change names etc.)
Column B - Job name and material/ labour header
Column C - Manually inserted to put job number
Column D - Journal number (always different)
Column E - Consistent. This will be either TIM for labour or P/L for materials if there is any on that row. It may be a case of this column to be a starting point as the job number will only need to appear when column E has either P/L or TIM in the cell.
Column F - Date posted to job costings (always different)
Column G - Description of materials or name of labourer

[TABLE="class: outer_border, width: 1000"]
<tbody>[TR]
[TD]CAP 15.06[/TD]
[TD]CAPITAL PROJECT - HOUSE BUILD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMLE-00001[/TD]
[TD]B&M GENERAL LABOUR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CAP 15.06[/TD]
[TD]T008000[/TD]
[TD]TIM[/TD]
[TD]02/07/2017[/TD]
[TD]SALLUCA J 2445[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CAP 15.06[/TD]
[TD]T008005[/TD]
[TD]TIM[/TD]
[TD]02/07/2017[/TD]
[TD]MALCA N 2638[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EXPENSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOB[/TD]
[TD]CAP 15.06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOB[/TD]
[TD]CAP 15.06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EX 577[/TD]
[TD]FUEL TANK INSTALLATION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMLE-00001[/TD]
[TD]B&M GENERAL LABOUR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EX 577[/TD]
[TD]T007985[/TD]
[TD]TIM[/TD]
[TD]25/6/17[/TD]
[TD]HENRY J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EX 577[/TD]
[TD]T007985[/TD]
[TD]TIM[/TD]
[TD]25/6/17[/TD]
[TD]BLOGGS K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EX 577[/TD]
[TD]T007987[/TD]
[TD]TIM[/TD]
[TD]25/6/17[/TD]
[TD]SHORT L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EXPENSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMME-00001[/TD]
[TD]B&M GENERAL MAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]EX 577[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]P008404[/TD]
[TD]P/L[/TD]
[TD]17/6/17[/TD]
[TD]ROLLER SLEEVES[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]EX 577[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]P008404[/TD]
[TD]P/L[/TD]
[TD]17/6/17[/TD]
[TD]RATCHET[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]EX 577[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]P008404[/TD]
[TD]P/L[/TD]
[TD]17/6/17[/TD]
[TD]TAPS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]EX 577[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]P008404[/TD]
[TD]P/L[/TD]
[TD]17/6/17[/TD]
[TD]CEMENT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EXPENSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOB[/TD]
[TD]EX 577[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOB[/TD]
[TD]EX 577[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ES 227[/TD]
[TD]WIRING OFFICE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMLE-00001[/TD]
[TD]B&M GENERAL LABOUR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ES 227[/TD]
[TD]T0007988[/TD]
[TD]TIM[/TD]
[TD]17/6/17[/TD]
[TD]MIDDLE J 2476[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Your help is appreciated.

Regards,

Liam
 
Upvote 0
Liam,

The code I developed was based on an assumption that Column A only contained Job #s, Material or Labour. I see that for Material Col A has BMME-00001 and for Labour is has BMLE-00001 so I can code for that rather than Material or Labour. The part I'm struggling with now is that there are other entries in Column A which renders my current approach useless. Is there any consistency to Job IDs? I see in this example there are Job IDs that start with CAP, EX and ES. If there is any consistency to a specific list of Job ID types then I could possibly code to that. Also, the other possibility is that for the samples you provided, each Job started with General Labour. If that is ALWAYS the case I could look for that to know when Jobs change.

Let me know, I really want to help you but need a new way to know a new Job has started.
 
Upvote 0
Frank,

You have been a massive help and could not have asked for more from anyone.

Kind Regards,

Liam
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
542
glaccounting
G
M
  • Solved
Split Material & Labour Cost within Macros
Replies
7
Views
966
StephenCrump
S
D
  • Question Question
The correct row is not being highlighted and having appropriate code run on that row
15 16 17
Replies
161
Views
8K
Michael M
Michael M
P
  • Question Question
VBA to create proposal sheet(s)
Replies
3
Views
1K
padadof2
P

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top