+ Reply to Thread
Results 1 to 9 of 9

How can I combine the data without it messing up?

  1. #1
    Registered User
    Join Date
    10-15-2019
    Location
    Arkansas
    MS-Off Ver
    Office 365 ProPlus Version 1902
    Posts
    4

    Post How can I combine the data without it messing up?

    I hope I added the attachment correctly. If I didn't... I am wanting to know if there is a simple way to combine the dates of a product to equal one total amount.

    For example, I deal with inventory and so most of my reports generate this (it separates each allocated date by the time- show I have to change it to Short Date in Format Cells):

    Item--Code-Quantity-Date

    Apples--XXX--20--9/10/19
    Apples--XXX--20--9/10/19
    Apples--XXX--20--10/10/19
    Apples--XXX--20--11/10/19
    Beans--XXX--57--7/15/18
    Carrots--XXX--10--8/27/19
    Carrots--XXX--10--8/27/19
    Carrots--XXX--10--10/27/19
    Carrots--XXX--10--10/27/19

    I need my table to combine the dates into one total like this:

    Apples--XXX--40--9/10/19
    Apples--XXX--20--10/10/19
    Apples--XXX--20--11/10/19
    Beans--XXX--57--7/15/18
    Carrots--XXX--20--8/27/19
    Carrots--XXX--20--10/27/19

    Can you help me? Or am I stuck going through each item manually?
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by bria100717; 10-15-2019 at 11:31 AM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,256

    Re: How can I combine the data without it messing up?

    Welcome to the Forum bria100717 !

    You attached your image correctly, but images have very limited value. Please attach your actual Excel file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-15-2019
    Location
    Arkansas
    MS-Off Ver
    Office 365 ProPlus Version 1902
    Posts
    4

    Re: How can I combine the data without it messing up?

    Thank you! I have uploaded the document!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,930

    Re: How can I combine the data without it messing up?

    Your dates in column D are really date/times, so it makes it difficult to get exact duplicates. Can these be converted just to dates?

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,930

    Re: How can I combine the data without it messing up?

    Never mind, I got round it by using SUMPRODUCT instead of COUNTIFS, but you need to insert a blank row above your data (which you could use for headings), so that your data starts on row 2. Then you can use this formula in E2:

    =IF(SUMPRODUCT((A$2:A2=A2)*(INT(D$2:D2)=INT(D2)))=1,MAX(E$1:E1)+1,"-")

    and copy down to the bottom of your data. Then you can use these formulae in the cells stated:

    G2: =IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$E:$E,0)),"")
    H2: =IFERROR(INDEX(B:B,MATCH(ROWS($1:1),$E:$E,0)),"")
    I2: =IF(G2="","",SUMPRODUCT(($A$2:$A$12=G2)*(INT($D$2:$D$12)=J2),$C$2:$C$12))
    J2: =IFERROR(INT(INDEX(D:D,MATCH(ROWS($1:1),$E:$E,0))),"")

    Format J2 at a date in the style you prefer, then copy these 4 formulae down as far as you need them (i.e. until you start to get blanks).

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    10-15-2019
    Location
    Arkansas
    MS-Off Ver
    Office 365 ProPlus Version 1902
    Posts
    4

    Re: How can I combine the data without it messing up?

    From the system it is generated from, no. It has date & time on every entry. Do you know of a way to remove the time?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,930

    Re: How can I combine the data without it messing up?

    See my follow-up in Post #5.

    Pete

  8. #8
    Registered User
    Join Date
    10-15-2019
    Location
    Arkansas
    MS-Off Ver
    Office 365 ProPlus Version 1902
    Posts
    4

    Re: How can I combine the data without it messing up?

    Thank you! I will definitely need to need to play with it. Hopefully, I can get it!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,930

    Re: How can I combine the data without it messing up?

    Basically, the formula in column E identifies the first record in any description/date combination and allocates a unique sequential number to those records. This helps to compile the unique list in columns G, H and J, and the formula in column I adds up the appropriate values for each unique grouping.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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] Cannot enter a column with Excel messing up all my data
    By exced in forum Excel General
    Replies: 2
    Last Post: 04-06-2016, 04:27 PM
  2. [SOLVED] Module name messing up UDF?
    By anon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2014, 04:37 PM
  3. Messing with percentages
    By sav1979 in forum Excel General
    Replies: 4
    Last Post: 05-12-2011, 01:11 AM
  4. sort messing up second workbook
    By benjii19 in forum Excel General
    Replies: 5
    Last Post: 03-17-2011, 06:58 PM
  5. [SOLVED] Tab character is messing me up
    By Some Dude in forum Excel General
    Replies: 4
    Last Post: 06-27-2006, 06:45 PM
  6. DDE Module Messing up Other Code
    By Lil Pun in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-26-2006, 01:25 PM
  7. Sort without messing up formulas
    By Ruth in forum Excel General
    Replies: 3
    Last Post: 01-26-2006, 10:10 AM

Tags for this Thread

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