Partially delete cell content, based on previous cell

[ibTops]

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


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]COLUMN S[/TD]
[TD="align: center"]COLUMN T[/TD]
[TD="align: center"]COLUMN U[/TD]
[TD="align: center"]COLUMN V[/TD]
[/TR]
[TR]
[TD="align: center"]01, 03, 21[/TD]
[TD="align: center"]01, 03, 21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]08, 15[/TD]
[TD="align: center"]08, 15, 08[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30, 33, 34[/TD]
[TD="align: center"]30, 33, 34, 30, 34[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]09[/TD]
[TD="align: center"]09[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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):

16, 35, 41	16, 35, 41, 35	16, 35, 41
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

<colgroup><col><col><col><col><col></colgroup><thead>
[TH="align: center"][/TH]
[TH="align: center"]S[/TH]
[TH="align: center"]T[/TH]
[TH="align: center"]U[/TH]
[TH="align: center"]V[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]16, 41[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]06, 09, 14, 16, 18, 21, 25, 28, 41[/TD]
[TD="align: right"] 03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45 [/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]

</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
16, 35, 41	16, 35, 41, 35
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]01, 03, 21[/TD]
[TD="align: center"]01, 03, 21[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01, 03, 21[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]08, 15[/TD]
[TD="align: center"]08, 15, 08[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]08[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]30, 33, 34[/TD]
[TD="align: center"]30, 33, 34, 30, 34[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]30, 34[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]16, 41[/TD]
[TD="align: center"]35[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]06, 09, 14, 16, 18, 21, 25, 28, 41[/TD]
[TD="align: center"]03, 35, 38, 39, 43, 45[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]V1[/TH]
[TD="align: left"]=listsubtract(S1,U1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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):

16, 35, 41	16, 35, 41, 35	16, 35, 41
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

<tbody>
[TH="align: center"][/TH]
[TH="align: center"]S
[/TH]
[TH="align: center"]T
[/TH]
[TH="align: center"]U
[/TH]
[TH="align: center"]V
[/TH]

[TD="align: center"]1
[/TD]

[TD="align: right"]16, 41
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]06, 09, 14, 16, 18, 21, 25, 28, 41
[/TD]
[TD="align: right"] 03, 06, 09, 14, 16, 18, 21, 25, 28, 35, 38, 39, 41, 43, 45
[/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]

</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.