Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #16  
Old 07-09-2017, 10:12 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 788
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default


Is it like the attached?
Attached Files
File Type: xlsx Raw DATA_2.xlsx (324.3 KB, 4 views)
Reply With Quote
  #17  
Old 07-10-2017, 12:29 PM
Kubi Kubi is offline Windows XP Office XP
Advanced Beginner
 
Join Date: May 2017
Posts: 32
Kubi is on a distinguished road
Default Thanks

Thank you, I have applied it to the sheet and have changed the cell it is working off however I do not think it is fully working please see attached.

There are cells returning a Zero instead of 1 or 2 etc

also as this formula essentially creates a new units column is it possible to have it return the sum as well as the individual numbers of the other cells or a zero if it has been summed? please see below.

i.e.

12345 - units 2 - count 0
12345 - units 5 - count 7
58244 - units 1 - count 1
48125 - units 9 - count 0
48125 - units 9 - count 18
Attached Files
File Type: xlsx RAW DATA.xlsx (397.4 KB, 7 views)
Reply With Quote
  #18  
Old 07-11-2017, 01:55 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 788
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

You wrote

I do not think it is fully working please see attached

But what is not working? Please be specific. I don't know what you mean by 12345, 58244 and 48125. None of these seems to be neither Product number nor Reference number.

I guess it is all completely obvious to you, but not to me.

It must be possible for you manually enter more (at least five) examples of expected results and specifically indicate which cells return wrong result.

Also I don't know what you mean by your last sentence.
(English is not my mother tongue which partly may explain my difficulties in understanding).
Reply With Quote
  #19  
Old 07-11-2017, 12:20 PM
Kubi Kubi is offline Windows XP Office XP
Advanced Beginner
 
Join Date: May 2017
Posts: 32
Kubi is on a distinguished road
Default Sorry

Sorry let me start again.

1. in the attached spreadsheet above you will see the formula does not seem to be adding up the units correctly, in some cells it is showing a zero instead of the sum of units.

2. secondly the codes I wrote were just examples of how I am trying to get it to work, if there is no duplicate unit counts than I need the formula to return the number of units shown.

3. However if there is a duplicate than I need the formula to return the sum of the units as it is doing now. please see below as an example of how I need it to work.

------------------sheet---Formula
------------------units----return
18175---SB-520384--1------ 0
18175---SB-520384--2------ 3
21222---SB-520384--1------ 1
40897---SB-520399--2------ 0
40897---SB-520399--1-------3
18173---SB-520384--1------ 1
18175---SB-520384--1------ 1

I hope I have made this all make sense.
Reply With Quote
  #20  
Old 07-11-2017, 10:35 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 75
ArviLaanemets is on a distinguished road
Default

Hi

Create an ODBC query with DISTINCT clause, which reads unique data from table with double rows and writes result to another sheet. You also can use SUM, AVEREGE, MIN, etc in query to get some aggregate values. Or you can use formulas like SUMIFS or COUNTIFS in adjacent columns to query table to calculate aggregate values from original data table.
Reply With Quote
  #21  
Old 07-11-2017, 11:02 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 788
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

But I tried to do as you showed in the file uploaded in #3.

Is it better now?
Attached Files
File Type: xlsx RAW DATA_3).xlsx (457.5 KB, 4 views)
Reply With Quote
  #22  
Old 07-12-2017, 01:06 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 75
ArviLaanemets is on a distinguished road
Default

I added edited RawData.xlsm file, where ODBC query is used to consolidate your data.

The format *.xlsm is used, as I added an Open event which corrects query parameters on open automatically, whenever the file name or location is changed. Without this this script allowed to run, you have to edit the query datasource manually every time you relocate or rename the file.

In your original table, I removed a couple of spaces from column names.

As source table for query I defined a non-dynamic named range nData. Currently it has 20000 rows - when your table has more rows, then edit nData.

Along with sum of Unit's I calculated also the number of deliveries and min of FromDate and max of ToDate.
Attached Files
File Type: zip RawData.zip (507.9 KB, 2 views)
Reply With Quote
  #23  
Old 07-12-2017, 04:00 PM
Kubi Kubi is offline Windows XP Office XP
Advanced Beginner
 
Join Date: May 2017
Posts: 32
Kubi is on a distinguished road
Default

Wow thank you this all looks very impressive, I am not to sure how to read it though.

I have just looked at the Raw data sheet and have had a play and it looks like i can work with this and use it to complete everything i need. thank you so much for this.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidating Rows with same Resource Name OTPM Excel Programming 2 05-30-2017 08:30 AM
Consolidating various word docs in one Max Downham Word 6 11-23-2015 05:07 PM
Consolidating Sentences into One Paragraph ctsolar Word 4 12-16-2013 04:50 PM
Consolidating data using Macro mrjamez Excel Programming 2 05-22-2012 06:50 AM
Help with consolidating multiple records into one wbiggs2 Excel 0 11-30-2006 01:02 PM


All times are GMT -7. The time now is 08:46 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft