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.

Column CColumn DColumn E
1PAPA1=2
2PAPA1=2
3TATA3=4
4TATA3=4
5TATA3=4
6FEAR6=7
7FEAR6=7
8MAMA8=9
9MAMA8=9
10MAMA8=9
11MAMA8=9
12GHMF12=13
13GHMF12=13

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

Please help me with the code.

Thanks in advance.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

Column CColumn DColumn E
1PAPA1=2
2PAPA1=2
3TATA3=4=5
4TATA3=4=5
5TATA3=4=5
6FEAR6=7
7FEAR6=7
8MAMA8=9=10=11
9MAMA8=9=10=11
10MAMA8=9=10=11
11MAMA8=9=10=11
12GHMF12=13
13GHMF12=13
14MATA
15PATA15=16
16PATA15=16

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
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.

Column CColumn DColumn E
1PAPA1=2
2PAPA1=2
3TATA3=4=5
4TATA3=4=5
5TATA3=4=5
6FEAR6=7
7FEAR6=7
8MAMA8=9=10=11
9MAMA8=9=10=11
10MAMA8=9=10=11
11MAMA8=9=10=11
12GHMF12=13
13GHMF12=13
14MATA
15PATA15=16
16PATA15=16

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
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

J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
253
Fluff
Fluff
K
  • Question
Fill any five cell only
Replies
3
Views
175
Peter_SSs
Peter_SSs
S
  • Question
Hide / unhide columns without using macros / VBA
Replies
1
Views
141
JEC
JEC
K
  • Question
Average based on importance level from 1-9
Replies
5
Views
136
Abdmujib
A
L
  • Question
Copy data from the previous upper line and paste to current row
Replies
0
Views
150
Linda Lo
L

Forum statistics

Threads
1,214,599
Messages
6,120,453
Members
448,967
Latest member
grijken

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