+ Reply to Thread
Results 1 to 11 of 11

how add function average to the this code and merge duplicate item?

  1. #1
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    734

    how add function average to the this code and merge duplicate item?

    hi
    so far I don't find solution to my case. it should calculate price average in column(5) in list box and column E based on the sheet data and merge duplicate items based on column(2) in listbox and column B based on sheet data , also the first column(1) in listbox should be 1,2,3 not date when merge the data . that's all of that happens when write the item in textbox1 based on column(2) in listbox and column B based on sheet data . with considering when calculate the averge then should column (6) in listbox= column(4) x column(5)
    note: merge duplicate item based on column(2) in listbox and column B in sheet of data and calculate price average in column(5) in list box and column E based on the sheet of data
    as you see in picture two . I want after select optionbutton show data as in show in the picture2
    pictur1 when search based on textbox1
    I issued also in this forum https://www.mrexcel.com/board/thread.../#post-5783230
    I hope finding solution here .

    HTML Code: 
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by ABDELFATTA; 11-07-2021 at 07:30 AM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: how add function average to the this code and merge duplicate item?

    This worked for a test Listbox.

    I ignored the LYD in the column for this test

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    734

    Re: how add function average to the this code and merge duplicate item?

    how works that? did you see the picture 2? . it should occurs when select option button and search in textbox1 then should merge and calculate the average price.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: how add function average to the this code and merge duplicate item?

    Please give me the numbers that you want to work with.

    is it (100*119 + 100*120)/200 = 119.5?

    Changing the figures

    is it (100*119 + 200*120)/200 = 119.6666667

    This returns the average price of the first two entries on your Table.
    [ It only loads the first two entries into the list box ]

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-07-2021 at 09:38 AM.

  5. #5
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    734

    Re: how add function average to the this code and merge duplicate item?

    the code doesn't work at all .when search in textbox . I want when I select option button .
    the search is in two cases . first normal when put the item in textbox show data in listbox , second when select optionbutton should calculate price average , merge items .

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: how add function average to the this code and merge duplicate item?

    Ahh Now I see what you are trying to achieve.

    But why does the first column show a 1? Not a Date?

    And what happens if different products are listed in the listbox.

    In your pictures both products are the same.
    Last edited by mehmetcik; 11-07-2021 at 09:47 AM.

  7. #7
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    734

    Re: how add function average to the this code and merge duplicate item?

    because it is merged when select option button. not need date . it became useless

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: how add function average to the this code and merge duplicate item?

    Ok

    This code should get you going.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    734

    Re: how add function average to the this code and merge duplicate item?

    I try implementing your code in my orginal file , but it gives error overflow in this line
    [HTML]

    T(5, 1) = Application.SumProduct(Q, P) / Application.Sum(Q)[HTML]

    can you implement for my file ,please?
    Last edited by ABDELFATTA; 11-08-2021 at 02:42 AM.

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: how add function average to the this code and merge duplicate item?

    I found your code over complicated and have rebuilt it for you.

    I am using two public varibles

    Mydata allows me to switch easily between Options

    CF is my ChangeFlag and allows me to avoid triggering parts of the code accidentally.

    For example I could use it to change the contents of Textbox1 without running the search code.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    734

    Re: how add function average to the this code and merge duplicate item?

    sorry for delaying to answer you .
    your solution is very helpful .
    thanks so much

+ 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] modified code merge duplicate items for each sheet separately based on this thread
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-16-2021, 12:30 AM
  2. [SOLVED] update or change owner of code merge duplicate items for each sheet separately
    By Hasson in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-15-2021, 07:33 AM
  3. merge duplicate data and summing value with average price
    By Alaa-A in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2021, 07:55 AM
  4. [SOLVED] Determine item count from the Average function?
    By aquixano in forum Excel General
    Replies: 22
    Last Post: 08-11-2019, 08:54 AM
  5. Problem...Is it possible to merge two workbooks by Item Code
    By hishamalbizri in forum Excel General
    Replies: 3
    Last Post: 06-23-2017, 03:26 AM
  6. Replies: 5
    Last Post: 03-01-2017, 05:41 PM
  7. How to merge duplicate rows with some unique data with VBA code
    By katkur in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-04-2013, 11:17 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