Combine cells but with a twist

[eechristaylor]

I am trying to be able to look at some invoice items and be able to tell if on seperate invoices the data I have is below. As you can see job #3575 has tasks 403 and 413 on it and 3785 only has 503 and 3996 has 600 and 800. In order to compare the job number and the task I thinkI need to add a column with both Job#-Task i.e. 3575-403, 3575-414. So how do I set that relationship up where excel knows the task in column c go to the job number above them in column b?

Your help is appreciated.

Invoice #	Job #	Task
1​	3575​
1​		403​
1​		414​
1​	3785​
1​		503​
2​	3996​
2​		600​
2​		800​

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D
1	Invoice #	Job #	Task
2	1	3575
3	1		403	3575-403
4	1		414	3575-414
5	1	3785
6	1		503	3785-503
7	2	3996
8	2		600	3996-600
9	2		800	3996-800
Summary

Cell Formulas
Range	Formula
D2:D9	D2	=IF(C2="","",LOOKUP(2,1/(B$2:B2<>""),B$2:B2)&"-"&C2)

 

[Peter_SSs]

If your Jobs are all 4 characters and task numeric, like all your samples, then another way would be as below.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

20 12 12.xlsm
	A	B	C	D
1	Invoice #	Job #	Task	Job #-Task
2	1	3575
3	1		403	3575-403
4	1		414	3575-414
5	1	3785
6	1		503	3785-503
7	2	3996
8	2		600	3996-600
9	2		800	3996-800
10
Job-Task

Cell Formulas
Range	Formula
D2:D10	D2	=IF(C2="","",IF(B1="",LEFT(D1,4),B1)&-C2)

 

[alansidman]

An alternative solution is to use Power Query. Here is the result and Mcode.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Job #"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Task] <> null))
in
    #"Filtered Rows"

Book3
	A	B	C
1	Invoice #	Job #	Task
2	1	3575	403
3	1	3575	414
4	1	3785	503
5	2	3996	600
6	2	3996	800
Table1