Posts by pangolin

pangolin · Apr 1st 2015

Re: Check cells content based on condition

see if this code helps

Code

Sub chkdc()
Set myrange = Sheet1.Range("C4", Range("C" & Rows.Count).End(xlUp))
For Each cell In myrange
If cell.Value = "DC" And cell.Offset(1, 0).Value <> "DC" And cell.Offset(-1, 0).Value <> "DC" Then cell.Offset(0, 7) = "ERROR"
If cell.Value = "DC" And cell.Offset(-1, 0).Value = "DC" Then
If cell.Offset(0, 2).Value <> cell.Offset(-1, 2) Or cell.Offset(0, 3).Value <> cell.Offset(-1, 3) Or cell.Offset(0, -2).Value <> cell.Offset(-1, -2) Then
cell.Offset(0, 7) = "ERROR"
cell.Offset(-1, 7) = "ERROR"
End If
End If
Next cell
End Sub

Display More

pangolin · Mar 31st 2015

Re: Unique cities based on several criterias

try the COUNTIFS formula...see this

=COUNTIFS($D$1:$D$7,$B$8,$E$1:$E$7,$A10,$B$1:$B$7,B$9)

pangolin · Mar 30th 2015

Re: Multiple criteria wit INDEX

one approach

=IFERROR(INDEX($C$1:$C$34,LARGE(ROW($A$2:$A$34)*(($A$2:$A$34)=$E2)*(($B$2:$B$34)=$F2),SUMPRODUCT((($A$2:$A$34)=$E2)*(($B$2:$B$34)=$F2))-(COLUMNS($G$1:G$1)-1))-ROWS($C$2)+1),"")

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

pangolin · Mar 27th 2015

Re: after insert new row formula automatically fills in cell question

yes for that you will have to format as TABLES...see "FORMAT AS TABLES" under HOME

see the help files or other posts for more info

pangolin · Mar 26th 2015

Re: Copy column to column designated by cell value

you can try this

Code

Sub copypaste()
Sheet1.Range("C2:C100").Copy Destination:=Sheet1.Range(Sheet1.Range("A1").Value)
End Sub

pangolin · Mar 25th 2015

Re: Random Team selection based on player cost, points

try SOLVER....see the forllowing steps

1.. Create 2 helper columns with as many rows as number of players
2...first helper col should only contain zeros
3...second helper col should be a product of the corresponding row of first helper col and the cost of plyers
4...Create two sum formula one for each helper col
5...set "Target Cell" to Value of 6 which is the sum of first helper column
6...then set the following three constraints
a. First helper col only to be set to Binary
b. Sum of second helper col should be less than or equal to 15
c. Sum of second helper col should be greater than or equal to 13

Then you SOLVE

I solved for the first 64 players and I got selection of player nos 2, 55, 56, 57, 59, 60 for a cost of 13

hope that helps

pangolin · Mar 23rd 2015

Re: VBA Code for Getting Divisior

see this

Code

Sub findmod()
Set myrange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each cell In myrange
If cell.Value Mod 3 = 0 Then cell.Offset(0, 1) = 3
Next cell
End Sub

develop similar code for the other validations and then post back ur code for the benefit of everybody

pangolin · Mar 23rd 2015

Re: Unique Values from multiple cells with delimiter into one cell

you can try this code

Code

Function str_split(myrange As Range, sep) As String
On Error Resume Next
Dim myarray()
For Each cell In myrange
For i = 1 To Len(cell.Value)
If Mid(cell.Value, i, 1) <> sep Then
If x = 0 Then
x = x + 1
ReDim Preserve myarray(1)
myarray(1) = Mid(cell.Value, i, 1)
End If
If x >= 1 Then
If WorksheetFunction.Match(Mid(cell.Value, i, 1), myarray(), 0) = False Then
x = x + 1
ReDim Preserve myarray(x)
myarray(x) = Mid(cell.Value, i, 1)
End If
End If
End If
Next i
Next cell
For i = 1 To x
If i = 1 Then
mystr = myarray(1)
Else
mystr = mystr & sep & myarray(i)
End If
Next i
str_split = mystr
End Function

Display More

pangolin · Mar 23rd 2015

Re: Not to show data if cell is blank

it is not very clear (atleast to me) what exactly you want to do but have you tried the ISBLANK function that you can use to determine which cell is blank

my suggestion to you would be that in your post you provide only the relevant explanation instead of describing functionality of ur worksheet which may not be germane to your immediate requirements

pangolin · Mar 23rd 2015

Re: Compare column A on two different worksheets, if match copy column b

see if this helps

=IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$27,0))=FALSE,INDEX(Sheet2!$B$1:$B$27,MATCH(A1,Sheet2!$A$1:$A$27,0)),"")

pangolin · Mar 12th 2015

Re: Match data from 2 columns and shift row to align

see if this code helps

Code

Sub matchcpy()
Dim myarray()
Set myrange1 = Sheets("sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
Set myrange2 = Sheets("sheet1").Range("B2", Range("B" & Rows.Count).End(xlUp))
On Error Resume Next
For Each cell In myrange2
If WorksheetFunction.IsError(WorksheetFunction.Match(cell.Value, myrange1, 0)) = True Then
For i = 0 To 3
cell.Offset(0, i).ClearContents
Next i
Else
x = x + 1
ReDim Preserve myarray(5, x)
myarray(1, x) = WorksheetFunction.Match(cell.Value, myrange1, 0)
For i = 0 To 3
myarray(i + 2, x) = cell.Offset(0, i).Value
cell.Offset(0, i).ClearContents
Next i
End If
Next cell
For p = 1 To x
For m = 2 To 5
Sheets("Sheet1").Range("B1").Offset(myarray(1, p), m - 2) = myarray(m, p)
Next m
Next p
End Sub

Display More

pangolin · Mar 11th 2015

Re: Pulling Data from other workbooks

this works for me

=SUMPRODUCT(((([wb2.xlsx]Sheet1!$F$1:$F$26)="a")+(([wb2.xlsx]Sheet1!$F$1:$F$26)="b"))*(([wb2.xlsx]Sheet1!$G$1:$G$26)=G1)*[wb2.xlsx]Sheet1!$H$1:$H$26)

note the syntax of using OR in SUMPRODUCT

pangolin · Mar 4th 2015

Re: MATCH a cell to a Range of a periodically updated list of records

after pasting fresh set of data you have to come to this sheet and copy paste this formula to the next 20 rows and after every 20 rows the range references in the formula change as you want....

this paste of formula is not VBA driven...you will have to do manually

and it is as per your first post where you said that you "prefer a non vba solution"

pangolin · Mar 4th 2015

Re: Check if range meets criteria if its not in criteria copy to different column

what I meant was either one of the two

Code

Sheet2.Range("A" & myCell.Row) = myCell.Value

OR

Code

Sheets("Sheet2").Range("A" & myCell.Row) = myCell.Value

OR

Code

myCell.EntireRow.Copy Destination:=Sheet2.Range("A" & myCell.Row)

pangolin · Mar 4th 2015

Re: Check if range meets criteria if its not in criteria copy to different column

just change this line

Code

Sheet1.Range("E" & cell.Row) = cell.Value

pangolin · Mar 4th 2015

Re: MATCH a cell to a Range of a periodically updated list of records

put this formula in 1st row and copy down

=IF(MATCH(A1,INDIRECT(ADDRESS(((ROUNDUP(ROW()/20,0)-1)*20)+1,1,,,"Sheet2")& ":"&ADDRESS(ROUNDUP(ROW()/20,0)*20,1)),0),"LAY","")

pangolin · Mar 3rd 2015

Re: If cell in an array is > value X return value in Row '3' ,Col 'X'

put this formula in Cell A4 and copy down

=IF(COUNTIF($B4:$M4,MAX($B4:$M4))>=2,1,IF(MAX($B4:$M4)<$D$1,0,OFFSET(A3,0,MATCH(MAX($B4:$M4),$B4:$M4,0))))

pangolin · Mar 3rd 2015

Re: Dynamically updating multiple scenario cashflow

not possible....see the attached

pangolin · Mar 3rd 2015

Re: Check if range meets criteria if its not in criteria copy to different column

this works for me

Code

Sub Copy() '
Dim dtRng As Range
Set dtRng = Sheet1.Range("C16", Range("C" & Rows.Count).End(xlUp))
For Each cell In dtRng
x = x + 1
If x >= 2 Then
If Not IsDate(cell.Value) Then
Sheet1.Range("E" & cell.Row) = cell.Value
End If
End If
Next cell
End Sub

Display More

pangolin · Mar 3rd 2015

Re: Dynamically updating multiple scenario cashflow

in Row 9 you can use this formula

=IF(AND(C5>=$G$3,C5<$C$3,C7>0),MAX(0,MIN(C7,$C$1+SUM($B$9:B9)))*-1,IF(C5=$C$3,SUM($B$9:B9)*-1,0))

note this formula assumes a T=0 column which is specifically inserted and is now Col B and therefore other values have been right shifted by one column

in Row 11 you can use this formula

=IF(C5=$G$3,$C$1*-1,IF(C5=$C$3,$C$1,0))

copy paste into other cells

hope that helps