# Posts by pangolin

• ## Data Processing Problem

Re: Data Processing Problem

is this what you want

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

put in cell J4 and copy paste down

• ## Find a ship date in one table that is >= a date in another table for a batch number.

Re: Find a ship date in one table that is &gt;= 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)

• ## Generate values in a table based on dropdown selection (complex data)

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))

• ## Unzip Files using VBA

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

• ## Unzip Files using VBA

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

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

• ## Count cells adjacent to same value until sum of those cells reaches certain value

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

• ## Number of working days in month following a date.

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

you can use this formula

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

• ## perform number format if command button is clicked

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()

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

• ## Transpose Rows to columns

Re: Transpose Rows to columns

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

• ## Future year asset replacement costs modelling

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

• ## Spread a total across specific months

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

• ## Finding irregularities in a pattern

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

• ## Back fill data for the week based on the friday value

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)))

• ## Excel "sudoku"

Re: Excel &quot;sudoku&quot;

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

• ## Optimization Constraints

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

• ## If Cell A1 = certain criteria how to extract the information from another sheet

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

• ## index and match question

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

• ## Counting with multiple entries in multiple columns

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

• ## Populate other cells based on specific criteria

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

• ## How to average column data based on row and column headers

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

this is one approach