Posts by pangolin

Re: Count Unique Records Meeting 2 Criteria with 2 possible values for Condition 1

for getting unique values use this formula

=SUM(IF(FREQUENCY(IF(($A$2:$A$14)<>"",IF((($B$2:$B$14)=$B$18),IF(((($C$2:$C$14)=$A$20)+(($C$2:$C$14)=$A$21)),MATCH($A$2:$A$14,$A$2:$A$14,0)))),ROW($A$2:$A$14)-ROW($A$2)+1),1))

note this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

Re: Count Unique Records Meeting 2 Criteria with 2 possible values for Condition 1

sorry posted wrongly....

Re: tiered pricing

why not use a simple IF formula like

=IF(A1<=1,750000,750000+(A1-1)*300000)

Re: Dynamic Chart for last 5 rows in Table

if there are non-numeric values appearing in the range then use this formula

=SUM(IF(ISNUMBER(A:A)=TRUE,(A:A),0)*IF((ROW(A:A))>(MAX(((A:A)<>"")*(ROW(A:A)))-5),1,0))

note this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

you are getting zero probably because the formula is not getting confirmed as above...or else post an example workbook in the forum for further assistance

Re: Dynamic Chart for last 5 rows in Table

this is one approach

this formula assumes no internediate blank rows and all numeric values

=SUM((A:A)*IF((ROW(A:A))>(MAX(((A:A)<>"")*(ROW(A:A)))-5),1,0))/5

change range references to suit

as usual it is an array formula and needs to be conformed by Ctrl+Shift+Enter

Re: Data Analysis Add in

probably REGRESSION in the form Y = a + bX

so you input a series of X and series of Y then compute the regression equation

then data analysis can will give the value oif a and b which can be used to predict the value of Y for a given value of X

note LINEST function will generate the same values for the regression while INTERCEPT can be used for 'a' while slope can be used for 'b'

Re: Extract text or integers from between parentheses

this is a formula based approach JIC someone needs it

=IF(LEN(A$1)-LEN(SUBSTITUTE(A$1,"(",""))<ROWS($A$1:$A1),"",MID(A$1,FIND("~",SUBSTITUTE(A$1,"(","~",ROWS($A$1:$A1)))+1,FIND("~",SUBSTITUTE(A$1,")","~",ROWS($A$1:$A1)))-FIND("~",SUBSTITUTE(A$1,"(","~",ROWS($A$1:$A1)))-1))

Re: Pareto Chart based on Pivot Table

not understood your post but is this what ur looking for

Re: Weighted Average with uneven splits

this is one approach

put this formula in cell D1

note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

after copying into cell D1 and conforming as above you can copy it down to the other cells

=INDEX($B$5:$B$8,SUM(IF(ISNUMBER($B$15:B15)=TRUE,1,0)))*C15/IFERROR(SUM(($C$15:$C$26)*IF(ROW($B$15:$B$26)>=LARGE(ROW($B$15:$B$26)*(ISNUMBER($B$15:$B$26)=TRUE),SUM(IF(ISNUMBER($B$15:$B$26)=TRUE,1,0))-(SUM(IF(ISNUMBER($B$15:B15)=TRUE,1,0))-1)),1,0)*IF(ROW($B$15:$B$26)<LARGE(ROW($B$15:$B$26)*(ISNUMBER($B$15:$B$26)=TRUE),SUM(IF(ISNUMBER($B$15:$B$26)=TRUE,1,0))-SUM(IF(ISNUMBER($B$15:B15)=TRUE,1,0))),1,0)),SUM(($C$15:$C$26)*IF(ROW($B$15:$B$26)>=LARGE(ROW($B$15:$B$26)*(ISNUMBER($B$15:$B$26)=TRUE),1),1,0)))

Re: Sum First Occurrence Based Upon Multiple Criteria

one apprach could be to create a helper column that will idientify the second appearance of a material with Stock in Col D

for eg create a helper column put this in row 2 and copy down

=IF(D2=$D$6,SUMPRODUCT((($A$2:A2)=A2)*(($D$2:D2)=$D$6)),0)

then you can calculate the stock using a simple formula

=SUMIFS(F2:F21,$D$2:$D$21,$D$6,$I$2:$I$21,1)

where column I2:I21 is the helper column...you can then copy this formula across for the various months

IMHO it will be difficult to create functionality of helper column within one cell

hope this helps

Re: Sum of multiple criteria in a column

use the function SUMIFS.....see the help file for further info and syntax

Re: Return a specific value of the cell after clicking Checkbox !

see this code

Private Sub CheckBox1_Click()
Sheets("sheet1").Range("I2").Value = Sheets("sheet1").Range("G2").Value
End Sub

Re: Make the range of an Array dynamic based on specified date

use this formula

=(PRODUCT(1+B5:INDEX(B5:M5,MATCH($B$2,$B$4:$M$4,0))%)-1)*100

note this is an array formula and requires to be confirmed by Ctrl+Shift+Enter

Re: Dynamic Chart for last 5 rows in Table

this is one approach

=INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))-5+ROWS($A$1:A1))

put it in lets say Cell B1 and copy down next four rows

this references entire column A...change range references to suit requirements

note this formula assumes that there are no intermediate blank rows within the 4 rows above the last occupied row in the column

if you have any internediate blank rows within the 4 coccupied rows above the last occupied row in the column then you can use this formula

=INDEX(A:A,LARGE((A:A<>"")*(ROW(A:A))*((ISBLANK(A:A))=FALSE),ROWS($A$1:A1)))

this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

Re: Group same dates and Sum up another column

see if this code helps

Sub countreport()
Dim myrange As Range, mydata As Range
Dim myoutput() As Variant
Set myrange = Sheets("sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
Set mydata = Sheets("sheet1").Range("B2", Range("B" & Rows.Count).End(xlUp))
Set ws = Worksheets("sheet1")
With ws
For Each cell In myrange
If WorksheetFunction.CountIf(Range(Cells(2, 1), Cells(cell.Row, 1)), cell.Value) = 1 Then
x = x + 1
ReDim Preserve myoutput(2, 0 To x)
myoutput(1, x) = cell.Value
myoutput(2, x) = WorksheetFunction.SumIf(myrange, cell.Value, mydata)
End If
Next cell
End With
For i = 1 To x
Sheets("sheet1").Range("J1").Offset(i, 0) = myoutput(1, i)
Sheets("sheet1").Range("J1").Offset(i, 1) = myoutput(2, i)
Next i
End Sub

Re: retain leading zeros in calculated cells

from what I have understood I can offer two options

1. You can use TEXT in formulas by couching the formulas using VALUE function for eg =SUM(VALUE(TEXT)))

2. If you want leading zeros to appear in charts then once you have constructed the chart then right click the series and go to >> Format Data Labels >> Number >> Custom and there you can set your custom format like 0000 which will show the series in the chart with leading zeros

hope that helps

Re: VBA to populate 2nd and 3rd listboxes based on 1st listbox data.

what you will have to do is create name range for each of the columns in database

for eg in the code below the names are in range myname
id in myid and dept in mydept and so on

then for Listbox1 for Rowsource property refer to myname

then put this code in the private sub Listbox1_change section

Private Sub ListBox1_Change()
If ListBox2.ListCount = 1 Then
ListBox2.AddItem WorksheetFunction.Index(Range("myid"), WorksheetFunction.Match(ListBox1.Value, Range("myname"), 0))
ListBox2.RemoveItem 0
End If
If ListBox2.ListCount = 0 Then
ListBox2.AddItem WorksheetFunction.Index(Range("myid"), WorksheetFunction.Match(ListBox1.Value, Range("myname"), 0))
End If
End Sub

what will happen is upon launch of user form the Listbox1 will get populated with the various names

when you select any name the above code will get triggered and the other two list boxes will get poplulated with the relevant values

make sure that Rowsource property of the other two List boxes is kept blank

hope that helps

Re: Summarize the Raw Materials for finished products from a production calendar

one approach can be to organise the raw data of Production Details into a matrix and also to organise the Raw Material Breakdown data correctly

then use matrix multiplication to generate the RM Consumption summary

this will eliminate all the extra sheets (although you will have to create one helper matrix)

see the attached file...the orgnaised data / matrix are highlighted....I have deleted the not reqd sheets to emphasise the point

hope that helps

Re: Separate Numbers From Within a Single Cell01

Quote from Ratiocination;725244

Sorry my mistake the number format is infact
Category - Custom
Type - hh:mm

Is this still possible to do?

if that is the case then using function HOUR and MINUTE one can extract the hh:mm values respectively

Re: Clear Column content based on Column heading

use this code instead

Sub clcont()
Set WS = Worksheets("sheet1")
With WS
Set myrange = .Range("A6", .Cells(6, .Columns.Count).End(xlToLeft))
For Each cell In myrange
If cell.Value = "Saturday" Or cell.Value = "Sunday" Then
mycol = cell.Column
Set myrow = .Cells(.Rows.Count, mycol).End(xlUp)
Range(Cells(7, mycol), Cells(myrow.Row, mycol)).ClearContents
End If
Next cell
End With
End Sub