Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-18-2017, 06:02 AM
OTPM OTPM is offline Formula Help Windows 10 Formula Help Office 2016
Expert
Formula Help
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default Formula Help

Hi

I am looking for some help with a formula for searching for duplicate entries in two adjacent columns and then adding "0.3" to each of the duplicate column values.
I have Data as follows:
Column D Column E
1 1
3 2


2 2
1 1

I want the duplicate row to become:
1.3 1.3


Any help would be appreciated.

Thanks in advance.

Tony
Reply With Quote
  #2  
Old 10-22-2017, 06:36 AM
Roger Govier Roger Govier is offline Formula Help Windows 10 Formula Help Office 2016
Novice
 
Join Date: Oct 2017
Location: Abergavenny, Wales, UK
Posts: 13
Roger Govier is on a distinguished road
Default

Hi

You can't do it in the same cell by formula, only with VBA.
For a formula solution, in cell G1 enter
=D1+(COUNTIF($D$11,D1)-1)*0.3 and copy down
and in H1
=E1+(COUNTIF($E$1:E1,E1)-1)*0.3

If you want to do it in the cells, then this code will change the values in columns D and E
Code:
Sub Add3()

    Dim lr As Long, i As Long, x As Long
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    For i = 1 To lr
        x = Application.CountIf(Range(Cells(1, "D"), Cells(i, "D")), Cells(i, "D"))
        If x > 1 Then
            Cells(i, "D") = Cells(i, "D") + (x - 1) * 0.3
        End If
        x = Application.CountIf(Range(Cells(1, "E"), Cells(i, "E")), Cells(i, "E"))
        If x > 1 Then
            Cells(i, "E") = Cells(i, "E") + (x - 1) * 0.3
        End If
    Next i

End Sub
Reply With Quote
  #3  
Old 10-22-2017, 06:38 AM
OTPM OTPM is offline Formula Help Windows 10 Formula Help Office 2016
Expert
Formula Help
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Many thanks I will give this a go tomorrow.
Thanks again.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
Formula Help if(and.. formula paxon Excel 8 05-19-2016 08:05 AM
IIf Formula jkdiesel Project 3 05-17-2016 01:01 AM
Need help with dragging a formula and changing a reference column as I drag the formula. LupeB Excel 1 10-22-2015 03:02 PM
Formula Help Help with formula please. AndrewSE Excel 3 04-05-2011 08:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft