How can I combine the data without it messing up?

**bria100717** · 10-15-2019, 09:54 AM

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?

**6StringJazzer** · 10-15-2019, 10:21 AM

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.

**bria100717** · 10-15-2019, 11:31 AM

Thank you! I have uploaded the document!

**Pete_UK** · 10-15-2019, 12:17 PM

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

**Pete_UK** · 10-15-2019, 12:33 PM

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

**bria100717** · 10-15-2019, 12:36 PM

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?

**Pete_UK** · 10-15-2019, 01:18 PM

See my follow-up in Post #5.

Pete

**bria100717** · 10-15-2019, 01:43 PM

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

**Pete_UK** · 10-15-2019, 01:51 PM

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

How can I combine the data without it messing up?

LinkBack

Thread Tools

Rate This Thread

Display

How can I combine the data without it messing up?

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Cannot enter a column with Excel messing up all my data

[SOLVED] Module name messing up UDF?

Messing with percentages

sort messing up second workbook

Tab character is messing me up

DDE Module Messing up Other Code

[SOLVED] Sort without messing up formulas

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions