# Posts by pangolin

• ## Check cells content based on condition

Re: Check cells content based on condition

see if this code helps

• ## Unique cities based on several criterias

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)

• ## Multiple criteria wit INDEX

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

• ## after insert new row formula automatically fills in cell question

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

• ## Copy column to column designated by cell value

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``````
• ## Random Team selection based on player cost, points

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

• ## VBA Code for Getting Divisior

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

• ## Unique Values from multiple cells with delimiter into one cell

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

you can try this code

• ## Not to show data if cell is blank

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

• ## Compare column A on two different worksheets, if match copy column b

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

• ## Match data from 2 columns and shift row to align

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

see if this code helps

• ## Pulling Data from other workbooks

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

• ## MATCH a cell to a Range of a periodically updated list of records

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"

• ## Check if range meets criteria if its not in criteria copy to different column

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)``
• ## Check if range meets criteria if its not in criteria copy to different column

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``
• ## MATCH a cell to a Range of a periodically updated list of records

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

put this formula in 1st row and copy down

• ## If cell in an array is > value X return value in Row '3' ,Col 'X'

Re: If cell in an array is &gt; 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))))

• ## Dynamically updating multiple scenario cashflow

Re: Dynamically updating multiple scenario cashflow

not possible....see the attached

• ## Check if range meets criteria if its not in criteria copy to different column

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

this works for me

• ## Dynamically updating multiple scenario cashflow

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