+ Reply to Thread
Results 1 to 21 of 21

Macro to Match Items in Col C

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Macro to Match Items in Col C

    I have values in Col C and need the values that match to be deleted as follows


    1) Where there is a value in Col C with say a positive value eg 2150 and there is a single value that is negative -2150 then the rows containing these values are to be deleted (I have highlighted all the items in yellow where the positive value and negative values equal to zero). I have also put numbers next to each of the yellow items that match off

    2) Where there are several values in Col C that sums to say R 925.45 and there are several other values that sums up to these but they are opposite in value for e.g. if the sum of several values is say R 25000 and there are negative values adding up to- R 25000 then these must be deleted. I have shown these with different colours where several say negative values sums up to several positive values and vice versa



    It would be appreciated if someone could kindly assist me
    Last edited by Howardc1001; 04-29-2020 at 02:52 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to Match Items in Col C


    • What is the maximum numbers of rows in your real workbook ?

    • Does your attachment have exactly the same layout than your real workbook ?
    Last edited by Marc L; 04-29-2020 at 05:45 AM. Reason: typo …

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to Match Items in Col C


    Your grey sample seems wrong or it does not match your explanation …

  4. #4
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    Hi Marc

    Thank for pointing out that the grey was incorrect. Have amended this


    The maximum number of rows will be 500

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    I have written code per my specification in post #1 , but it only deleted the row sin yellow i.e. where there is only a single positive and one single negative value that matches , but not where there are multiple debit values that sum up to multiple credits values i.e if one has for g three values that total 525.25 and multiple credit values that totals -525.25 then these rows must be deleted

    It would be appreciated if someone could amend my code per my specification in post #1

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macro to Match Items in Col C


    Can't be amended, good to trash as combinatorics calculation is necessary so a totally new code !
    Or you can try just operating the Excel Solver feature or find existing solutions on Web …

    Help belongs to good enough readers as I posted two questions and I received only one answer
    so like for a space rocket launch, all lights must be green and here I'm still with a red one !

    According to your too small attachment versus your real data, it's better to join a 'big data' sample
    and respecting the original workbook layout or you're enough confident with your Excel / VBA skills
    to amend any code you may receive …

    In addition, for a smart enough reconciliation you may ask to Dumb or Dumber - the one who creates / exports the data -
    to add at least a column for ID, invoice #, … whatever but which helps a lot to reconciliate the appropriate amounts
    according to the accoutancy / financial rules as amounts alone are like a brand new car without any wheel …
    Last edited by Marc L; 05-04-2020 at 01:11 PM. Reason: Typo & an add …

  7. #7
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    Hi Marc


    Thanks for getting back to me and fully understand your predicament


    I am attaching another workbook with references, which I hope you will be able to assist



    1) Where there is a value in Col C with say a positive value eg 2150 and there is a single value that is negative -2150 then the rows containing these values are to be deleted (I have highlighted all the items in yellow where the positive value and negative values equal to zero). I have also put numbers next to each of the yellow items that match off

    2) Where there are several debit balances (have highlighted in different colours) with the same reference then these must be matched to credit balances and vice versa (have indicated the matching with numbers in Col I so you can see how these match off)


    3) There may be values where the sum of the debits and credits balance to zero, but they can have mixed references




    With regard to point # 1 above, I have written code which matches the Debit & credit values for a single item, but point # 2 & 3 is above is too complicated for me to write the code



    Please Login or Register  to view this content.
    Last edited by Howardc1001; 04-30-2020 at 11:02 AM.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Macro to Match Items in Col C


    Quote Originally Posted by Howardc1001 View Post
    I am attaching another workbook with references
    So where is it ? Not in your last post …

  9. #9
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    Sorry Marc-Forgot to upload amended file

    Have just uploaded it

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to Match Items in Col C


    As the reference does not match Debit & Credit like in your blue sample …

    I'm lost with the grey one starting in cell E20, what is the logic to follow ?

  11. #11
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    The debits for eg in Blue all have the same reference but the credit will not have the same reference

    With regard to the Grey items the ,references or name the same, but the value all total to zero and therefore the rows containing these items can be deleted

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macro to Match Items in Col C


    So the grey sample it out of the initial scope, as an unique Debit sum for several Credit amount or vice versa …
    … so any of my procedures can work with this case which is not the same as the initial post.
    For this grey sample according to accountancy / financial rules it must have an unique reference.

  13. #13
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    Marc you are 100% correct regarding accountancy/financial rules but the data is imported for several companies to reconcile the intercompany loan accounts so the references in this instance are not unique

    The coding may be very complicated and if so, don't spend much time on it. Would be nice to have as would save a lot of manual ticking

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macro to Match Items in Col C


    Even between companies it must obviously exist a matching reference …
    Without, Excel is far not the best tool as the more amounts, the more combinations,
    the more time to execute then Excel or Windows may crash …

  15. #15
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    I could not agree with you more on this

    What I am going to do it to get the people processing the info to use the a common ref in the narrative field

    I have attached a sample of what it will look like.

    I would be appreciated if you could provide me with code so where the narrative contains text with the same or similar text for eg the one may contain 191 shirts and the other 191 shirts other, then those rows re to be deleted
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Macro to Match Items in Col C


    Yes, it's easier like this but again your file is too small to compare differents ways,
    as it is the usual combinatorics way well works with such few data, like for your initial attachment.

    So is it always a Debit amount as sum and the combination is to search in Credit column or it's for both sides ?

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this Excel basics …


    According to your last attachment just using the Excel basics like any beginner can yet operates manually :

    PHP Code: 
    Sub Demo0()
         
    Dim V
             Application
    .ScreenUpdating False
        With Range
    ("A2", [A1].End(xlDown)).Resize(, 7).Columns
            
    .Item(7).Formula "=ROUND(SUMIF(" & .Item(6).Address ",F2," & .Item(5).Address "),2)=0"
            
    .Sort .Cells(7), xlAscendingHeader:=xlNo
             V 
    Application.Match(True, .Item(7), 0)
             If 
    IsNumeric(VThen .Rows(":" & .Rows.Count).Clear
            
    .Item(7).Clear
        End With
             Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄

  18. #18
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    Thanks for the help Marc. Code works perfectly

  19. #19
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Try this Excel basics …

    hi Marc

    You helped me while back with code see your post # 17

    Where there is a partial match in the narrative in Col F and the value in Col E is opposite & equal then the rows are to be deleted. I have highlighted the rows to be deleted in yellow


    Kindly amend your code
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macro to Match Items in Col C


    Post #17 code can't work as all rows contain 'shirts" and total is different than 0 …

  21. #21
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Match Items in Col C

    Thanks for letting me know

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to delete certain items then count remaining items #2
    By elgato74 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-21-2019, 02:26 PM
  2. [SOLVED] Macro to delete certain items then count remaining items
    By elgato74 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-14-2019, 03:20 PM
  3. Replies: 2
    Last Post: 11-17-2016, 06:37 AM
  4. [SOLVED] Need to count items in column that match multiple data items
    By bclucas55 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-27-2014, 10:03 AM
  5. macro to filter all items in Autofilter except 2+ items
    By DanRiverBrew in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2013, 09:15 PM
  6. [SOLVED] One to many items match
    By Pavan_Jack in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-30-2012, 01:16 PM
  7. [SOLVED] Match Items
    By vharjani3 in forum Excel General
    Replies: 17
    Last Post: 06-04-2012, 09:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1