Hi Rev and all.

Would you have an answer to this excel issue I have?

Hi all,

I was hopeing someone could advise me on how to attack my issue.

Column A is the quantity of products. In there are number values.
Column B has other items that are also number values and signify lengths of items.
For this example the range would be A3:B7.

I need to combine all the same length values is Column B and then sum their quanties.
For this example the range of the output would be D3:D* (however many cells are required) preferably in largest to smallest.

Finally, I need to concatenate the cells of column D into another cell

I need to do this on various spreadsheets and in different cells on spreadsheet. I really don’t want to use a pivot table because I need it dynamic and there are other issues that arise from that for me.

Here is a screenshot of what I mean. This is a very basic one. http://i37.photobucket.com/albums/e85/sanquar/Internet/Excel%20issue_zpsrkumyslx.jpg

Can anyone offer any assistance?

• I need to combine all the same length values is Column B and then sum their quanties.

SUMIF?

Are you saying that you want to sum the unique values, excluding from the sum the repeated values?

I can’t think of a straightforward way, but there is a not-too-complicated method here:
http://interactiveds.com.au/SSSF/AddSame.xlsb

The data is in A3:B34
Cell E3 finds the maximum value in column B, then cell D3 uses CountIf to find the number of occurrences of that value (5).
Cell E4 then uses the Large function to find the 6th largest value, i.e. the 2nd largest different value, and cell D4 finds the number of occurrences of that value, and so on until the minimum value. Column F then uses SumIf to find the total of column A for each different value in Column E.

For concatenating all the values as shown in your example, the simplest way I can think of is doing it row by row, as shown. It would be quite easy to write a VBA function to do it all in a single cell, so please let me know if you would like me to do that.

Also let me know if I haven’t interpreted what you want to do correctly.

sorry for the late response. internet outage here. I cobbled something together that is not very elegant, but does the job.

Thought you might use it in future.

Sub combine() Dim x With Range(“d9”, Range(“d” & Rows.Count).End(xlUp)) ‘ This cell needs to be the first line for the member lengths. x = Filter(Evaluate(“transpose(if(countif(offset(” & .Address & “,,,row(1:” & _ .Rows.Count & “)),” & .Address & “)=1,” & .Address & “))”), False, 0) End With With .Resize(UBound(x) + 1) ‘this is where I want the combined values to be placed. .Columns(2).Value = Application.Transpose(x) .Columns(1).Formula = “=sumif($D$9:$D$60,D70,$C$9:$C$60)” ‘ look at second column of dat (d9 to d60) starting at cell d9 and if there is a match get the quantity info from Cell c on the same row sum. .Sort .Cells(1, 2), 2 ‘ cells get sorted largest to smallest. = Join(Application.Transpose(Evaluate(.Columns(1).Address & _ “&”“/”“&” & .Columns(2).Address)), “, “) ‘ combine all the combined figures into one cell largest to smallest. End With
End Sub

Assuming 2 columns of data starting at row 9 and ending at row 60. Column c is qty and column d is length.

My combined and summed cells start at row 70 in columns c and d. The concatenated data is added into cell a69.

Thanks again for replying folks. No sleep made it all a bit too hard yesterday.

and thank you for the spreadhseet… I will look at it after I get my desk clear.

cheers,

Suffering from Excel blues again

Things I want to see in Excel:

1. Unhide all.
I have 15 hidden worksheets and I don’t want to have to go though and unhide them all one at a time.
2. Open as worksheet.
Excel only opens text files as separate individual excel files when I want to open them all as worksheets in the given open Excel file.
3. Windows to worksheets.
Move multiple open files to worksheets from a drop-down menu instead of having to tile the display and swat each fly individually. This used to be a feature of Excel before they lost it.
4. Spline fit to data
5. Smoothing spline fit to data
6. Contour plots!
7. Revert to the graph style from superseded version of Excel that defaulted to colours that made sense – primary and secondary colours, and had automatic chart title and axis titles (modern version defaults wrongly to no chart title or axis titles).
8. Single button to convert chart curve palette to greyscale
9. Single button to convert chart curves to dashed lines in B/W
10. Command “gather” copies across every nth line or nth column from data range.
11. Command “scatter” reverses “gather”.
12. Command “trendline” available outside of charts, to produce automatic polynomial and exponential fits to data.
13. Type legend directly (without having to go through edit data source).
14. Greek characters available in chart and axis titles.
15. Data analysis bolt-on automatic with Excel installation
16. Fix the “histogram” command so that it no longer needs a “bin” data range input. Make bin automatic, and can be set to logarithmic size with single mouse click.
17. Fix automatic chart ranges to allow for “tight” ranges, minimising whitespace beyond all extremes of data.
18. Fix chart axis position so a single click sets the axis at minimum value (WTF does it currently only allow for the bloody useless single click maximum values).
19. Single command to adjust the sizes of all markers and all lines for all data sequences on a chart simultaneously.
20. Remember that I want to open each text file delimited by spaces, rather than forcing me to change the default file open every single time.
21. Autohide useless operations that I never use on all the toolbars.
22. Remember favourite chart options (such as font size) so I don’t have to retype them in every time.

mollwollfumble said:

2. Open as worksheet.
Excel only opens text files as separate individual excel files when I want to open them all as worksheets in the given open Excel file.

You can import a text file into a worksheet. See “Get External Data” on the “Data” tab. In VBA, see the “QueryTable” object or the “QueryTables” collection.

I prefer to import a text file as a QueryTable rather than open a text file because I can specify the data type of each field as Text which is important in cases where one has ID numbers that should not be converted to a numeric data type.

KJW said:

I prefer to import a text file as a QueryTable rather than open a text file because I can specify the data type of each field as Text which is important in cases where one has ID numbers that should not be converted to a numeric data type.

It is also important in cases where there are date fields that have the potential to be misinterpreted as American style format.

mollwollfumble said:

1. Unhide all.
I have 15 hidden worksheets and I don’t want to have to go though and unhide them all one at a time.

That could be done quite simply with VBA.

> You can import a text file into a worksheet.
Thanks, but that’s not the same. I want to open 20 text files as 20 worksheets. I’ll look into it though.

23. Excel should not generate a “blue screen of death”. On this morning’s BSOD from Excel, logging in again created four Excel windows whereas only one was open before. That’s a new bug. (Another new bug is that after BSOD “open in new window” from Chrome is misbehaving).
24. On re-opening after crash, Excel must delete all junk files, including ~$file.xlsx
25. Copy-paste and Cut-paste must allow paste more than once before the clipboard is emptied, simply typing text from keyboard must not clear the clipboard. (In old Excel this used to work for Copy).
26. Allow exponential and logarithmic trendline with zero and negative data values (these values ignored by trendline algorithm)
27. Allow logarithmic axis with zero and negative data values (these values not plotted)
28. Copy and paste should work within “edit series” of chart “select data source”
29. On charts, extra clickable option “duplicate series”
30. On axis data ranges, the “manual” button serves no purpose, typing a value into the axis range suffices without the need to click “manual” first (In old Excel this used to work)
31. When automatically setting window position to a range, such as for when selecting data to be used for y axis, start the window one row higher than needed, so the user can see the start of the data range.
32. Automatically expand and contract the size of the legend box when the series name is changed.
33. In borders, allow an automatic border option that puts a vertical line before and after the first column, before and after the first row, and after the last row and column.

KJW said:

mollwollfumble said:

1. Unhide all.
I have 15 hidden worksheets and I don’t want to have to go though and unhide them all one at a time.

That could be done quite simply with VBA.

Please let me know how to do any or all of the abive in VBA.

My mood is not helped by:
a) The data I’m plotting in Excel doesn’t fit the theoretical model. It’s supposed to be an exponentiual decay with a mean inversely proportional to an input parameter. It looks more like two straight lines and the mean is nowhere near inversely proportional to the input parameter.

b) This morning’s BSOD destroyed Chrome formatting of the holiday forum so badly that I’m typing this in a text box measuring 6 mm high by 35 mm wide.

> 16. Fix the “histogram” command so that it no longer needs a “bin” data range input. Make bin automatic, and can be set to logarithmic size with single mouse click.

> 21. Autohide useless operations that I never use on all the toolbars.

16. Also on histogram, don’t want to be forced to specify output data range. Should run without changing font type and without adding cell borders.

21. (This used to work in an old version of Office).

34. Change autoselect word so that each punctuation mark is treated as a separate word, eg. at present it is impossible to select text within a pair of brackets.

Help on expanding this text box I’m now typing in to bigger than 6 mm by 35 mm is urgently needed.

35. Give duplicate sheets (and files for that matter) a decent name, not a “ (1)”, “ (2)” addition because that just means I have to rename every sheet.

36. Don’t “autocorrect” series names from for example, 3.5% to 3.50%
37. Removing trandlines by using undo should be a 1 step process not a 3-step process (the 3 steps are: remove equation on chart, go back to linear, then remove).

mollwollfumble said:

36. Don’t “autocorrect” series names from for example, 3.5% to 3.50%
37. Removing trandlines by using undo should be a 1 step process not a 3-step process (the 3 steps are: remove equation on chart, go back to linear, then remove).

I’ll have a look at the other 36 points later, but for spline functions you can download from:
https://newtonexcelbach.wordpress.com/2012/10/08/daily-download-22-splines-and-curves/

The Rev Dodgson said:

mollwollfumble said:

36. Don’t “autocorrect” series names from for example, 3.5% to 3.50%
37. Removing trandlines by using undo should be a 1 step process not a 3-step process (the 3 steps are: remove equation on chart, go back to linear, then remove).

I’ll have a look at the other 36 points later, but for spline functions you can download from:
https://newtonexcelbach.wordpress.com/2012/10/08/daily-download-22-splines-and-curves/

From link:
“Since it was first published in 2009 my CSpline function has been the most popular download from this site. CSpline is used to fit a series of cubic curves through specified points, allowing interpolation along a smooth curve. The latest version of the spreadsheet is:
http://www.interactiveds.com.au/software/CSpline2.zip
The original function is described at Cubic Splines. This was further developed in response to a reader query at Using Cubic Splines in Practice.”

Ex-cell-ent.

Now do a smoothing spline. ;-)
The algorithm is at: “Numerische Mathematik 10, 177- 183 (1967) Smoothing by Spline Functions” by CHRISTIAN H. REINSCH.
This paper contains a free copy of a program that does the interpolation (in Algol).

mollwollfumble said:

The Rev Dodgson said:

mollwollfumble said:

36. Don’t “autocorrect” series names from for example, 3.5% to 3.50%
37. Removing trandlines by using undo should be a 1 step process not a 3-step process (the 3 steps are: remove equation on chart, go back to linear, then remove).

I’ll have a look at the other 36 points later, but for spline functions you can download from:
https://newtonexcelbach.wordpress.com/2012/10/08/daily-download-22-splines-and-curves/

From link:
“Since it was first published in 2009 my CSpline function has been the most popular download from this site. CSpline is used to fit a series of cubic curves through specified points, allowing interpolation along a smooth curve. The latest version of the spreadsheet is:
http://www.interactiveds.com.au/software/CSpline2.zip
The original function is described at Cubic Splines. This was further developed in response to a reader query at Using Cubic Splines in Practice.”

Ex-cell-ent.

Now do a smoothing spline. ;-)
The algorithm is at: “Numerische Mathematik 10, 177- 183 (1967) Smoothing by Spline Functions” by CHRISTIAN H. REINSCH.
This paper contains a free copy of a program that does the interpolation (in Algol).

Try: https://newtonexcelbach.wordpress.com/2014/10/06/excelpython2-alglib-and-spline-matrix-rel-2-03/

Also Scipy does smoothing splines, and xlScipy lets you access them from Excel:
https://newtonexcelbach.wordpress.com/2016/03/20/xlscipy-with-xlwings/

mollwollfumble said:

35. Give duplicate sheets (and files for that matter) a decent name, not a “ (1)”, “ (2)” addition because that just means I have to rename every sheet.

How does an automated program know what is a “decent name”?

As for naming worksheets, that’s done by:

ThisWorkbook.Worksheets(1).Name = “Name”

Note that this only renames the sheet tab name and the name in the Worksheets collection. It doesn’t change the name in the object list in the VBA interface. That can’t be changed programmatically (at least not without programming the VBE).

mollwollfumble said:

KJW said:

mollwollfumble said:

1. Unhide all.
I have 15 hidden worksheets and I don’t want to have to go though and unhide them all one at a time.

That could be done quite simply with VBA.

Please let me know how to do any or all of the abive in VBA.

Well, I can’t help you with ALL of the above, but the “Unhide all” can be done as follows:

Dim wb As Workbook
‘Define wb object (eg, Set wb=ThisWorkbook)
Dim ws As Worksheet
For Each ws In wb.Worksheets
ws.Visible = xlSheetVisible
Next ws