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

[Liam1]

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.

HB 100	House Build - Joe Bloggs
Material
		Cladding
		Timber
		Cement
Labour
		F Mayweather

<tbody>
</tbody>

 

[frank_AL]

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.

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

 

[Liam1]

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?

 

[frank_AL]

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.

 

[Liam1]

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

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

<tbody>
</tbody>

Your help is appreciated.

Regards,

Liam

 

[frank_AL]

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.

 

[Liam1]

Frank,

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

Kind Regards,

Liam