Partially delete cell content, based on previous cell

[ibTops]

I'm dealing with 4 Columns with the following initial layout example:

COLUMN S	COLUMN T	COLUMN U	COLUMN V
01, 03, 21	01, 03, 21
08, 15	08, 15, 08	15
30, 33, 34	30, 33, 34, 30, 34	33
09	09

<tbody>
</tbody>

I'd need, via either an equation or a macro, that the cells in Column V show the numbers I've highlighted in BLUE in each row.

Basically what's happening is that:

- If Column U = Empty, then Column T = Column S. This is fine because I do V = T, done.

- If Column U = NotEmpty, then Column T = Column S + (Column S - Column U). This is a problem, because what I'd need in Column V in these cases is only the part: Column S - Column U.


I hope that makes sense. Thanks in advance!!

 

[Eric W]

Will column U always just be one number? If so, try:

Book1
	S	T	U	V
2	01, 03, 21	01, 03, 21		01, 03, 21
3	08, 15	08, 15,08	15	08
4	30, 33, 34	30, 33, 34,30, 34	33	30, 34
5	9	9		9
Sheet1

Cell Formulas
Range	Formula
V2	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(","&SUBSTITUTE(SUBSTITUTE(S2,",",",,")," ","")&",",","&U2&","," "),","," "))," ",", ")

If not, I suspect you'll need a macro.

 

[jtakw]

My formula needs adjustment, will post back.

 

[jtakw]

My formula needs adjustment, will post back.

Sorry, got busy, here's my version, hope I understand correctly:

Excel 2010
	S	T	U	V
1	01, 03, 21	01, 03, 21		01, 03, 21
2	08, 15	08, 15,08	15	08
3	30, 33, 34	30, 33, 34,30, 34	33	30, 34
4	09	09		09
5	21, 22, 23, 24	21, 22, 23, 24, 25	21	22, 23, 24
6	21, 22, 23, 24	21, 22, 23, 24, 25	25	21, 22, 23, 24
Sheet4

Cell Formulas
Range	Formula
V1	=IF(U1="",S1,TRIM(SUBSTITUTE(SUBSTITUTE(" , "&S1,", "&U1,"")," , ","")))

 

[ibTops]

Thanks Eric, but I'm afraid Column U can be more than one number

AWESOME jtakw!! That's working great, except for these 2 examples (IDK why??? I've highlighted in orange the numbers I'm getting in V):

	S	T	U	V
1	16, 35, 41	16, 35, 41, 35	16, 41	16, 35, 41
2	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45, 03, 35, 38, 39, 43, 45	06, 09, 14, 16, 18, 21, 25, 28, 41	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Any ideas??

 

[Eric W]

I believe jtakw's formula also only works when there is one value in U. You'll need a UDF.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu click Insert > Module. Paste the following code into the window that opens:

Public Function ListSubtract(Orig As String, Remov As String)
Dim x As Variant, y As String, x1 As Variant

    x = Split(Replace(Orig, " ", ""), ",")
    y = "," & Replace(Remov, " ", "") & ","
    
    For Each x1 In x
        If InStr(y, "," & x1 & ",") = 0 Then ListSubtract = ListSubtract & x1 & ", "
    Next x1
    
    ListSubtract = Left(ListSubtract, Len(ListSubtract) - 2)
End Function

Press Alt-Q to close the editor. Back on your Excel sheet, enter this formula:

Excel 2012

	S	T	U	V
1	01, 03, 21	01, 03, 21		01, 03, 21
2	08, 15	08, 15, 08	15	08
3	30, 33, 34	30, 33, 34, 30, 34	33	30, 34
4	9	9		9
5	16, 35, 41	16, 35, 41, 35	16, 41	35
6	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45, 03, 35, 38, 39, 43, 45	06, 09, 14, 16, 18, 21, 25, 28, 41	03, 35, 38, 39, 43, 45

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula V1 =listsubtract(S1,U1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope this helps.

 

[jtakw]

Thanks Eric, but I'm afraid Column U can be more than one number

AWESOME jtakw!! That's working great, except for these 2 examples (IDK why??? I've highlighted in orange the numbers I'm getting in V):

	S	T	U	V
1	16, 35, 41	16, 35, 41, 35	16, 41	16, 35, 41
2	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45, 03, 35, 38, 39, 43, 45	06, 09, 14, 16, 18, 21, 25, 28, 41	03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45

<tbody>
</tbody>

Sheet4​

Any ideas??

My formula will work with either a single 2 digit number Or multiple 2 digit numbers separated by a coma and space in Column U; however, it will Only work if the set of numbers in Column U is in the Exact same order as in Column S with no Other numbers in between, as in these samples you showed above, where my formula Will fail.

So you're better off with Eric's UDF in that situation.