Posts by pangolin

pangolin · Jan 12th 2015

Re: Data Processing Problem

is this what you want

=(((2*G4+2*H4)*I4)+1)*2

put in cell J4 and copy paste down

pangolin · Jan 7th 2015

Re: Find a ship date in one table that is >= a date in another table for a batch numb

this is one approach

=IFERROR(INDEX(tblShipStatus[ShipDate],SUMPRODUCT(ROW(tblShipStatus[JobNum])*(tblShipStatus[JobNum]=Table_DMR_ShipStatus[[#This Row],[Batch]])*(tblShipStatus[ShipDate]>=Table_DMR_ShipStatus[[#This Row],[DMR Date]])*(tblShipStatus[PartsQuantity]=Table_DMR_ShipStatus[[#This Row],[Shipped Quantity]]))-1),"")

however this formula would need to be tweaked if there are multiple shipped quantity with same values on different dates (currently it seems that is not happening)

pangolin · Dec 18th 2014

Re: Generate values in a table based on dropdown selection (complex data)

from what I have understood

First...you create a List using Data Validation (Data Validation>>Settings>>List wher you select cols B1 to F1)

Second...suppose the list has been created in Cell B28 then

in Cell H3 you put this formula and copy paste down

=INDEX(B3:F3,,MATCH($B$28,$B$1:$F$1,0))

pangolin · Dec 10th 2014

Re: Unzip Files using VBA

thanx for your replies....Holycow that worked fantastic...many thanx

I got confused in using the "Dir" command and hence getting a run time error when I was providing this as a value

I have also removed the line for creation of new folder

the new code is here for benefit of everybody

Code

Sub filesToUnzip()
    Dim oApplicationlication As Object
    Dim fileName As Variant
    Dim folderFileName As Variant
    fileName = "{File name with location}"
    folderFileName = "{location}"
    Set oApplication = CreateObject("Shell.Application")
    oApplication.Namespace(folderFileName).CopyHere oApplication.Namespace(fileName).items
    DoEvents
    MsgBox "You have extracted the zip files to **", vbInformation
End Sub

Display More

pangolin · Dec 9th 2014

Hi Friends...

probably this question has been asked earlier but I am not able to find a fix for the same

this code for unzipping files works great excpet that it requires the user to select the file...I wanted the same to be automated

Code

Sub filesToUnzip()
Dim oApplicationlication As Object
Dim fileName As Variant
Dim folderFileName As Variant
fileName = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)
folderFileName = "C:\UnzippedFiles" & "\"
MkDir folderFileName
Set oApplication = CreateObject("Shell.Application")
oApplication.Namespace(folderFileName).CopyHere oApplication.Namespace(fileName).items
DoEvents
MsgBox "You have extracted the zip files to C:\UnzippedFiles\", vbInformation
End Sub

Display More

specifically can someone tell me how exactly to change this line

Code

fileName = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)

since if I supply the filename and path as a strng then I get runtime error

help appreciated

thanx

pangolin · Nov 20th 2014

Re: Count cells adjacent to same value until sum of those cells reaches certain value

I am not saying this is the only way and I hope some Member comes out with a combined formula but one approach can be create a handler column in this case in Col I

where you put this formula

=SUM(($E$2:E2)*(($D$2:D2)=D2)) and copy down

then on Col G you can put this formula

=SUM((($D$2:$D$73)=D2)*(($I$2:$I$73)<=F2))

this will give you the necessary values...(pl check again)

Note both these formulae are array formula and need to be conformed by Ctrl+Shift+Enter

pangolin · Oct 27th 2014

Re: Number of working days in month following a date.

you can use this formula

=NETWORKDAYS(EOMONTH(A1,0),EOMONTH(A1,1))

pangolin · Oct 22nd 2014

Re: perform number format if command button is clicked

why dont you try couching it within commandbutton_click

for eg

Code

Private Sub CommandButton1_Click()


'' Place your code here


End Sub

then whenever you click the commandbutton the code will get executed

the commnadbutton_click will be autoimatically available when you go into the UserForm in VBA and click the commandbutton

pangolin · Oct 22nd 2014

Re: Transpose Rows to columns

so why dont u just use the TRANSPOSE function...see the help files for more info

pangolin · Oct 22nd 2014

Re: Future year asset replacement costs modelling

in your example I havent understood why you havent included the value of 951 for 2024-25 in your calculations......as per what I have understood from your logic that value should also be included

having said that see this formula

=SUM(OFFSET($F$4,IF(ROW(F4)-9>(ROW($F$4)-1),ROW(F4)-7-ROW($F$4),1)-1,):F4)/3*550

put it in Row 5 and copy down

this works after including the value for year 2025 as above

see the attached file....the formula is highlighted therein

pangolin · Oct 16th 2014

Re: Spread a total across specific months

this is one method

=IF(F$1=$C2,$E2-(ROUNDDOWN($E2/($C2-$B2+1),0)*($C2-$B2)),IF(AND(F$1>=$B2,F$1<$C2),ROUNDDOWN($E2/($C2-$B2+1),0),0))

although for the last month it leaves a higher remainder......check if thats ok or else I will tweak the formula likewise

pangolin · Oct 16th 2014

Re: Finding irregularities in a pattern

not sure if this is really what you want but see if this helps

=IF(CODE(C3)-CODE("a")+1=IF(MOD(ROW(C3)-ROW($C$3)+1,7)=0,7,MOD(ROW(C3)-ROW($C$3)+1,7)),"REG","IRREG")

it starts from Cell C3 and cehcks if alphabets a to g come in the correct sequence, if yes it will give REG else IRREG

one can even use it to conidtionally format

pangolin · Oct 14th 2014

Re: Back fill data for the week based on the friday value

this is one approach

=IF(ISERROR(MATCH(A5,$A$1:$A$2,0))=TRUE,INDEX($B$1:$B$2,MATCH(A5,$A$1:$A$2,1)+1),INDEX($B$1:$B$2,MATCH(A5,$A$1:$A$2,0)))

pangolin · Oct 14th 2014

Re: Excel "sudoku"

Quote

CAN EXCEL EVEN DO THIS?!

Not sure if you have heard of SOLVER utility within excel???

using that I got the following values for your problem

A1>>91 B1>>275 C1>>134 B2>>250 A3>>9 B3>>275 C3>>66

remaining cells zero...try it

pangolin · Oct 13th 2014

Re: Optimization Constraints

do you really expect soimeone to reply to this kind of post

upload a file with this data and the results you expect if you want someone to seriously consider your post

pangolin · Oct 13th 2014

Re: If Cell A1 = certain criteria how to extract the information from another sheet

you can use something like this

Code

Sub sheetmac()
Dim mysheet As Variant
mysheet = Sheets("sheet1").Range("A1").Value
Sheets("sheet1").Range("C5") = Sheets(mysheet).Range("A1")
End Sub

a formula based approach would work like this

=INDIRECT(ADDRESS(1,1,,,A1))

pangolin · Oct 8th 2014

Re: index and match question

you can use this formula

=INDEX($C$2:$C$23,MATCH($E3&F$2,$A$2:$A$23&$B$2:$B$23,0))

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

also for some combimnations the value does not exist and this will return an NA error hence couch this formula within IFERROR

pangolin · Oct 8th 2014

Re: Counting with multiple entries in multiple columns

you can use this formula

=SUMPRODUCT(((Sheet1!$A$3:$A$14)=A3)*IF((ISNUMBER(Sheet1!$H$3:$H$14)+ISNUMBER(Sheet1!$I$3:$I$14)+ISNUMBER(Sheet1!$J$3:$J$14))>0,1,0))

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

pangolin · Oct 7th 2014

Re: Populate other cells based on specific criteria

use this formula

=INDEX($G$2:$G$297,LARGE(ROW($A$2:$A$297)*(($A$2:$A$297)=A2)*(($E$2:$E$297)=$E$9),1)-ROW($F$2)+1)

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

also note that do not use this formula in Column G as it will replace the manual entries in that column....therefore use it in another column lets say H

pangolin · Sep 25th 2014

Re: How to average column data based on row and column headers

this is one approach

=AVERAGE(INDIRECT(ADDRESS(MATCH($A$2,Sheet1!$A$2:$A$6,0)+ROW(Sheet1!$A$2)-1,COLUMN(Sheet1!$B$1),,,"Sheet1")&":"&ADDRESS(MATCH($A$2,Sheet1!$A$2:$A$6,0)+ROW(Sheet1!$A$2)-1,MATCH($B$1,Sheet1!$B$1:$H$1,0)+COLUMN(Sheet1!$B$1)-2)))