Cell values based on comparison of previous column cell value.

D

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
hi All,

I want formula or macro that work on the following table and provide the value in column E. The macro should compare the sorted (a-z) value in column D and based on the value from the column C, it provide the below mentioned details in column E.

[TABLE="width: 242"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TATA[/TD]
[TD]3=4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TATA[/TD]
[TD]3=4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TATA[/TD]
[TD]3=4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
</tbody>[/TABLE]

Please help me with the code.

Thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Sep53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("D2", Range("D" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
        .Item(K).Offset(, 1) = .Item(K)(1).Offset(, -1).Value _
        & "=" & .Item(K)(2).Offset(, -1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
An alternative:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Sep32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c, fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range("c2", Range("c" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not fd And Dn.Offset(1, 1).Value = Dn.Offset(, 1).Value [COLOR="Navy"]Then[/COLOR]
       txt = Dn.Value & "=" & Dn.Offset(1).Value
       fd = True
    [COLOR="Navy"]ElseIf[/COLOR] Not Dn.Offset(1, 1).Value = Dn.Offset(, 1).Value [COLOR="Navy"]Then[/COLOR]
       fd = False
    [COLOR="Navy"]End[/COLOR] If
        Dn.Offset(, 2).Value = txt
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
For a formula solution, does this do what you want?
I have included a few more rows because I wasn't sure if it is possible for you to have a single row like row 15 and, if so, what you want in column E.

<b>Compare values</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76px;" /><col style="width:76px;" /><col style="width:76px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Column C</td><td style="font-size:10pt; ">Column D</td><td style="font-size:10pt; ">Column E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; ">PAPA</td><td style="font-size:10pt; ">1=2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; ">PAPA</td><td style="font-size:10pt; ">1=2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; ">TATA</td><td style="font-size:10pt; ">3=4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; ">TATA</td><td style="font-size:10pt; ">3=4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; ">TATA</td><td style="font-size:10pt; ">3=4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; ">FEAR</td><td style="font-size:10pt; ">6=7</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; ">FEAR</td><td style="font-size:10pt; ">6=7</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; ">MAMA</td><td style="font-size:10pt; ">8=9</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; ">MAMA</td><td style="font-size:10pt; ">8=9</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">MAMA</td><td style="font-size:10pt; ">8=9</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">11</td><td style="font-size:10pt; ">MAMA</td><td style="font-size:10pt; ">8=9</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; ">GHMF</td><td style="font-size:10pt; ">12=13</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">13</td><td style="font-size:10pt; ">GHMF</td><td style="font-size:10pt; ">12=13</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; ">ABC</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">15</td><td style="font-size:10pt; ">DEF</td><td style="font-size:10pt; ">15=16</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">16</td><td style="font-size:10pt; ">DEF</td><td style="font-size:10pt; ">15=16</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IF(D2=D1,E1,IF<span style=' color:008000; '>(D2=D3,C2&"="&C3,"")</span>)</td></tr></table></td></tr></table> <br /><br />


I note that if a row like 15 is possible, that Mick's post 3 code returns "12=13" for that row but the post 2 code returns a blank as my formula does.
 
Last edited:
Upvote 0
Peter_SSs said:
For a formula solution, does this do what you want?
I have included a few more rows because I wasn't sure if it is possible for you to have a single row like row 15 and, if so, what you want in column E.

Compare values
Click to expand...
Hi peter & Mick,

Thank you for your reply. both answer are working fine, however there is slight change in my requirement. Please find below the desired output.

[TABLE="width: 242"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TATA[/TD]
[TD]3=4=5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TATA[/TD]
[TD]3=4=5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TATA[/TD]
[TD]3=4=5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]MATA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]PATA[/TD]
[TD]15=16[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]PATA[/TD]
[TD]15=16[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
MickG said:
An alternative:-

Regards Mick
Click to expand...

Hi Mick,

Thank you for the code. Its working fine, however there is slight change in my requirement. Please refer to the desired output below. The same is also mentioned in reply to Peter.

[TABLE="width: 242"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TATA[/TD]
[TD]3=4=5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TATA[/TD]
[TD]3=4=5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TATA[/TD]
[TD]3=4=5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]MAMA[/TD]
[TD]8=9=10=11[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]MATA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]PATA[/TD]
[TD]15=16[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]PATA[/TD]
[TD]15=16[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Deepk said:
.. there is slight change in my requirement. Please find below the desired output.
Click to expand...
To achieve this by standard worksheet formulas, you will need the TEXTJOIN function which is only available in very recent Excel versions. If you don't have that function then you will need a vba solution.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
CDE
1Column CColumn DColumn E
21PAPA1=2
32PAPA1=2
43TATA3=4=5
54TATA3=4=5
65TATA3=4=5
76FEAR6=7
87FEAR6=7
98MAMA8=9=10=11
109MAMA8=9=10=11
1110MAMA8=9=10=11
1211MAMA8=9=10=11
1312GHMF12=13
1413GHMF12=13
1514MATA
1615PATA15=16
1716PATA15=16
Compare values (3)
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Sep22
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("D2", Range("D" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
  [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
        .Item(K).Offset(, 1) = Join(Application.Transpose(.Item(K).Offset(, -1).Value), "=")
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Peter_SSs said:
To achieve this by standard worksheet formulas, you will need the TEXTJOIN function which is only available in very recent Excel versions. If you don't have that function then you will need a vba solution.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Thank you Peter for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Character count, multiplication and based on cell value
Replies
9
Views
347
Manoj K
M
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
291
Alex Blakenburg
A
E
  • Question Question
If statement
Replies
4
Views
120
xlhelp15
xlhelp15
S
  • Solved
Table comparison using formulas
Replies
2
Views
113
sharshra
S

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

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