Fantasy Football - Footballguys Forums
This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!
***Official*** Excel Help Corner (1 Viewer)
- Thread starter Ned
- Start date
Ned
Footballguy
If you don't have a lot of formulas, you can just nest it all together. I'm assuming you want to see the index results OR a blank, depending on what's found......
=IF(ISERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))),"",INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)))
if you have a lot of formulas, I'd add a secondary column to do the IF/ISERROR test since the indexing is redundant in the option above (Excel will choke on calculating). This assumes your INDEX/MATCH is in A1.
=IF(ISERROR(A1),"",A1)
Spin
Footballguy
Spin
Footballguy
Here's a solution that will handle just numbers as well as letters.Kanil said:
First, you'll have to make your own macro. I've used this one before that this guy wrote:
Function StringConcat(Sep As String, ParamArray Args()) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' www.cpearson.com/Excel/stringconcatenation.aspx
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula. There is a VBA imposed limit that
' a string in a passed in array (e.g., calling this function from
' an array formula in a worksheet cell) must be less than 256 characters.
' See the comments at STRING TOO LONG HANDLING for details.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
If Len(R.Text) > 0 Then
S = S & R.Text & Sep
End If
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
On Error GoTo 0
Err.Clear
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M
Else
''''''''''''''''''''''''''''''''''''''''''''''''
' STRING TOO LONG HANDLING
' Here, the error handler must be set to either
' On Error GoTo ContinueLoop
' or
' On Error GoTo ErrH
' If you use ErrH, then any error, including
' a string too long error, will cause the function
' to return #VALUE and quit. If you use ContinueLoop,
' the problematic value is ignored and not included
' in the result, and the result is the concatenation
' of all non-error values in the input. This code is
' used in the case that an input string is longer than
' 255 characters.
''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ContinueLoop
'On Error GoTo ErrH
Err.Clear
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
Err.Clear
M = LBound(Args(N), 2)
If Err.Number = 0 Then
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M
End If
On Error GoTo ErrH:
End If
Else
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
End If
Else
On Error Resume Next
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
On Error GoTo 0
End If
ContinueLoop:
Next N
'''''''''''''''''''''''''''''
' Remove the trailing Sep
'''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
If Len(S) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
End If
StringConcat = S
'''''''''''''''''''''''''''''
' Success. Get out.
'''''''''''''''''''''''''''''
Exit Function
ErrH:
'''''''''''''''''''''''''''''
' Error. Return #VALUE
'''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' www.cpearson.com/Excel/stringconcatenation.aspx
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula. There is a VBA imposed limit that
' a string in a passed in array (e.g., calling this function from
' an array formula in a worksheet cell) must be less than 256 characters.
' See the comments at STRING TOO LONG HANDLING for details.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
If Len(R.Text) > 0 Then
S = S & R.Text & Sep
End If
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
On Error GoTo 0
Err.Clear
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M
Else
''''''''''''''''''''''''''''''''''''''''''''''''
' STRING TOO LONG HANDLING
' Here, the error handler must be set to either
' On Error GoTo ContinueLoop
' or
' On Error GoTo ErrH
' If you use ErrH, then any error, including
' a string too long error, will cause the function
' to return #VALUE and quit. If you use ContinueLoop,
' the problematic value is ignored and not included
' in the result, and the result is the concatenation
' of all non-error values in the input. This code is
' used in the case that an input string is longer than
' 255 characters.
''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ContinueLoop
'On Error GoTo ErrH
Err.Clear
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
Err.Clear
M = LBound(Args(N), 2)
If Err.Number = 0 Then
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M
End If
On Error GoTo ErrH:
End If
Else
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
End If
Else
On Error Resume Next
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
On Error GoTo 0
End If
ContinueLoop:
Next N
'''''''''''''''''''''''''''''
' Remove the trailing Sep
'''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
If Len(S) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
End If
StringConcat = S
'''''''''''''''''''''''''''''
' Success. Get out.
'''''''''''''''''''''''''''''
Exit Function
ErrH:
'''''''''''''''''''''''''''''
' Error. Return #VALUE
'''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
End Function
Just copy, open excel, press Alt + F11 -> Insert -> Module. Paste this into the window.
Then go into your excel and in A1 type =StringConcat("",A2:UI2). Copy down.
This will concatenate everything within a row, since you said there's only one entry, and the rest are blanks, A1 will only have that one entry. Integer / string / etc
skol asylum
Footballguy
Just copy, open excel, press Alt + F11 -> Insert -> Module. Paste this into the window.
Then go into your excel and in A1 type =StringConcat("",A2:UI2). Copy down.
This will concatenate everything within a row, since you said there's only one entry, and the rest are blanks, A1 will only have that one entry. Integer / string / etc
Just use HLOOKUP like i posted. No need for all of this.
Spin
Footballguy
Just copy, open excel, press Alt + F11 -> Insert -> Module. Paste this into the window.
Then go into your excel and in A1 type =StringConcat("",A2:UI2). Copy down.
This will concatenate everything within a row, since you said there's only one entry, and the rest are blanks, A1 will only have that one entry. Integer / string / etc
Just use HLOOKUP like i posted. No need for all of this.
Then go into your excel and in A1 type =StringConcat("",A2:UI2). Copy down.
This will concatenate everything within a row, since you said there's only one entry, and the rest are blanks, A1 will only have that one entry. Integer / string / etc
Just use HLOOKUP like i posted. No need for all of this.
Ned
Footballguy
skol asylum
Footballguy
Just use HLOOKUP like i posted. No need for all of this.
You're right.
John Bender
Footballguy
General question for a budget spreadsheet I created.
Right now I have the BUDGET Page and an ACTUAL (Spent) Page...I have to monitor both to keep under budget on all my categories
So at the top I put my income for every pay date and then down the below, the bills/expenses that fall between that pay date and the next one.
Actually I'm pretty proud of it as it calculates how much is left over has anything from Haircut to Savings Contributions (I then track my savings account on yet a third page).
So...basically, my haircut budget is 25 dollars a month, let's say it costs 23 dollars. I have to click on the ACTUAL page and type in 23, and then go back to my BUDGET page and reduce the "budget" down to 23 showing that I stayed under after the pay period is up.
Is there a way to combine the actual and budget pages so that I can log expenses all on the same page with an additional line but have it not affect all the numbers (until I need to recalculate the "budget" at the end of the pay period)
For things like gas expenses, I might have to log in an expense 3 times, what I do now is basically continually keep a running overall total on the ACTUAL page where it might be easier to have new lines on the BUDGET page each time I purchase the gas heading.
This makes no sense, I know...
If someone wants to take a look at it, and clean it up/make it a bit more dynamic, I'd be happy to pay a fee for your time. I know it can be done, but right now such an unabridged method that it becomes cumbersome.
I know there are budget templates out there, but I like the one I created: it's clean, and instead of monthly, operates between pay periods (every 2 weeks).
Right now I have the BUDGET Page and an ACTUAL (Spent) Page...I have to monitor both to keep under budget on all my categories
So at the top I put my income for every pay date and then down the below, the bills/expenses that fall between that pay date and the next one.
Actually I'm pretty proud of it as it calculates how much is left over has anything from Haircut to Savings Contributions (I then track my savings account on yet a third page).
So...basically, my haircut budget is 25 dollars a month, let's say it costs 23 dollars. I have to click on the ACTUAL page and type in 23, and then go back to my BUDGET page and reduce the "budget" down to 23 showing that I stayed under after the pay period is up.
Is there a way to combine the actual and budget pages so that I can log expenses all on the same page with an additional line but have it not affect all the numbers (until I need to recalculate the "budget" at the end of the pay period)
For things like gas expenses, I might have to log in an expense 3 times, what I do now is basically continually keep a running overall total on the ACTUAL page where it might be easier to have new lines on the BUDGET page each time I purchase the gas heading.
This makes no sense, I know...
If someone wants to take a look at it, and clean it up/make it a bit more dynamic, I'd be happy to pay a fee for your time. I know it can be done, but right now such an unabridged method that it becomes cumbersome.
I know there are budget templates out there, but I like the one I created: it's clean, and instead of monthly, operates between pay periods (every 2 weeks).
skol asylum
Footballguy
Could you have a page where you enter expenses down one column and to the left you tag each with an expense category. On the actual/budget page you have your categories in column A, your actual in column B with sumifs looking at the expense input page, and your budget in column C, and your remaining budget in column D.
John Bender
Footballguy
This is pretty much the idea here - just making it more easy to navigate and dynamic.
Someone here has reached out to take a look for me (thank you again).
Spin
Footballguy
Yah, shoot me an email and I'll be happy to look at it for you if the other person isn't able to help you out.
Dinsy Ejotuz
Footballguy
Ok, here's one...
I want to copy this chunk of code, but change the arrays in the INDEX functions to avoid having to redo the full line of code each time.
=IF(X$5=$Q9,"perm",IF(X8="perm","perm",IFERROR(MIN(MIN(X$6:X8),INDEX($D$6:$M$15,$Q9,X$5)+INDEX($AC8:$AL8,1,$Q9)),"---")))
Right now when I try to change the cells being referenced it won't take them. Anyone know how to do this?
ETA: Duh. Nevermind. You do it the same way you do when you enter them (CTRL + SHIFT + ENTER).
I want to copy this chunk of code, but change the arrays in the INDEX functions to avoid having to redo the full line of code each time.
=IF(X$5=$Q9,"perm",IF(X8="perm","perm",IFERROR(MIN(MIN(X$6:X8),INDEX($D$6:$M$15,$Q9,X$5)+INDEX($AC8:$AL8,1,$Q9)),"---")))
Right now when I try to change the cells being referenced it won't take them. Anyone know how to do this?
ETA: Duh. Nevermind. You do it the same way you do when you enter them (CTRL + SHIFT + ENTER).
Last edited by a moderator:
Spin
Footballguy
Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.
[SIZE=11pt]=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")[/SIZE]
[SIZE=11pt]I'd like to be able to just =Averageif($E$5:$E$17,PSSVSS,L$5:L$17) But it doesn't work. It only matches the first entry within the named range. [/SIZE]
[SIZE=11pt]I know my other one worksjust fine, but it's more just bugging me at this point.[/SIZE]
[SIZE=11pt]=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")[/SIZE]
[SIZE=11pt]I'd like to be able to just =Averageif($E$5:$E$17,PSSVSS,L$5:L$17) But it doesn't work. It only matches the first entry within the named range. [/SIZE]
[SIZE=11pt]I know my other one worksjust fine, but it's more just bugging me at this point.[/SIZE]
Ned
Footballguy
I don't think that's bassackwards. Without seeing the sheet - is this some sort of weighted average?
I can't see a way of making it work for either AVERAGEIF or AVERAGEIFS.
Spin
Footballguy
No, it's not weighted. But the number of entries within "PSSVSS" may change, so I can't just only average a certain range. Well I could. but this way I can just add/subtract from the named range and not have to worry about updating a ton of formulas to only hit certain rows, if that makes sense.
Ned
Footballguy
I think what you did is best. The only other way is to enter a ton of specific criteria in AVERAGEIFS.
TheWalkmen
Footballguy
Is there a non-vba way to lock multiple cell formulas at one time? Like if I am pulling totals from a different tab from cells C4 & C5 can I lock it as $C$4 and $C$5 at the same time?
Ned
Footballguy
Hitting F4 while editing the formula would lock all cells in that formula. You can toggle F4 multiple times to lock just rows or columns.
Corporation
Angry Bovine
Dot for later
TheWalkmen
Footballguy
Gawain
Footballguy
Column A has 1000 distinct values, Column B has 1500 distinct values, 950 of which match column A.
While keeping the data in separate columns, can I have Excel sort so that like values stay on the same row and that values that do not match are on separate rows and the whole thing is alphabetical?
While keeping the data in separate columns, can I have Excel sort so that like values stay on the same row and that values that do not match are on separate rows and the whole thing is alphabetical?
hagmania
Footballguy
I might learn something new here, because I don't have a one or two-click solution.
Are the matching values on the same row? I take it they're not because otherwise the sort function would do everything you need.
Quickest way I think:
I think you'll have to use a couple of helper columns using your choice of matching formula.(MATCH, VLOOKUP, COUNTIF)
Assuming data starts in A2:
1) C2. =IFERROR(VLOOKUP(B2, A:A, 1, 0), "") and Copy all the way down.
2) D2. =IFERROR(VLOOKUP(A2, C:C, 1, 0), A2
...
Ugh that's really gross. I'd restructure the table to a binary 1 or 0 type deal.
Column A = all unique values of both columns of the table in your example
Column B = 1 if qualifies to be in Column A of the table in your example, 0 if not
Column C = 1 if qualifies to be in Column B of the table in your example, 0 if not
Are the matching values on the same row? I take it they're not because otherwise the sort function would do everything you need.
Quickest way I think:
Ugh that's really gross. I'd restructure the table to a binary 1 or 0 type deal.
Column A = all unique values of both columns of the table in your example
Column B = 1 if qualifies to be in Column A of the table in your example, 0 if not
Column C = 1 if qualifies to be in Column B of the table in your example, 0 if not
Last edited by a moderator:
hagmania
Footballguy
Is this something you'll have to do repeatedly? Or just a one-shot type deal.
Edit: Actually the binary table is the quickest way to solve your immediate problem.
Alright -
1) Combine all values from Column A and B into Column C
2) Remove Duplicates from Column C and Sort Column C alphabetically
3) D2. =IFERROR(VLOOKUP(C2, A:A, 1, 0), "") copy down
4) E2. =IFERROR(VLOOKUP(C2, B:B, 1, 0), "") copy down
5) Copy/Paste values in D and E
6) Columns D and E are now your properly sorted/matched Columns A and B.
Edit: Actually the binary table is the quickest way to solve your immediate problem.
Alright -
1) Combine all values from Column A and B into Column C
2) Remove Duplicates from Column C and Sort Column C alphabetically
3) D2. =IFERROR(VLOOKUP(C2, A:A, 1, 0), "") copy down
4) E2. =IFERROR(VLOOKUP(C2, B:B, 1, 0), "") copy down
5) Copy/Paste values in D and E
6) Columns D and E are now your properly sorted/matched Columns A and B.
Last edited by a moderator:
Gawain
Footballguy
Once a week.
What I currently do is conditional format to highlight duplicate values and then just cut things into shape.
I can't combine the columns into one and sort, or else I'd do that and run a pivot table to count number of entries.
A BABA ABAACA ACAADA AEAAEA AFAAGA AGAAHA AIAAIA AJAAKA AKAHow can I keep duplicates on the same row (along with the data columns C-XX, tied to column B), while placing singles on their own row with either no data (single from column A) or relevant data (single in column B)
What I currently do is conditional format to highlight duplicate values and then just cut things into shape.
I can't combine the columns into one and sort, or else I'd do that and run a pivot table to count number of entries.
A BABA ABAACA ACAADA AEAAEA AFAAGA AGAAHA AIAAIA AJAAKA AKAHow can I keep duplicates on the same row (along with the data columns C-XX, tied to column B), while placing singles on their own row with either no data (single from column A) or relevant data (single in column B)
shadyridr
Footballguy
use access
2Young2BBald
Footbaldguy
Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
Ned
Footballguy
Assuming your date column is in col A....
Insert a new column B (or wherever) and use this "=TEXT(MONTH(A2),"mmm")" and then pivot on col B. If you want the full month name, change the "mmm" to "mmmm".
2Young2BBald
Footbaldguy
I get a #NAME in the column I created the formula. I'll keep dinking with it. If I want MMM-YY as I have 2014 & 2105 data, can I replace MMM with MMM-YY? Thanks for the quick reply!!!
eta, I jacked the quotes, its working now but only for the month, still need to get it to MMM-YY if possible.
Last edited by a moderator:
Dinsy Ejotuz
Footballguy
Anyone up in here know R too?
If so, any idea why this isn't working?
h = nrow(Electric)
Electric$Month <- 0
for(i in 1:12){
k=i
for(j in 1:h){
if (j==k){
Electric$Month[j] <- i
k=k+12 }}}
ETA: after messing around with it I think the problem is in the 5th row -- where I'm trying to assign values for the column Electric$Month.
ETA2: Fixed.
If so, any idea why this isn't working?
h = nrow(Electric)
Electric$Month <- 0
for(i in 1:12){
k=i
for(j in 1:h){
if (j==k){
Electric$Month[j] <- i
k=k+12 }}}
ETA: after messing around with it I think the problem is in the 5th row -- where I'm trying to assign values for the column Electric$Month.
ETA2: Fixed.
Last edited by a moderator:
Ned
Footballguy
2Young2BBald
Footbaldguy
Even easier is to just use the group option in the Pivot Table. With dates, it will automatically group by month, year, quarter, week, etc.
bryhamm
Footballguy
2Young2BBald, one thing to keep in mind is that when you format something in Excel, you are just displaying the number differently from a visual pov. The number/data is still unchanged. That was why you were getting what you were getting in the pivot table ... because the underlying data had not changed. Just an FYI.
2Young2BBald
Footbaldguy
Thanks for the replies, I'm self taught in Excel (and obviously not very well). Where do I find the group option? Do I drag my date field to the row labels and then select it there, or do I do the grouping first and then add it as a row label? I see filters in the choose fields to add to the report section, but none that appear to specifically allow me to select the date parameters for the report.
ETA, never mind, Google helped me to figure out its as simple as clicking on a date field and THEN clicking on group. This is insane functionality.
Last edited by a moderator:
Brony
Footballguy
I encounter similar situations and I find it just as easy to add a month column to my data. If dates are in Column A, then my month column would be =DATE(YEAR(A1),MONTH(A1),1)
I agree for this situation that pivot table grouping is fine - just pointing out a different solution for times when you don't want to use a pivot table.
I agree for this situation that pivot table grouping is fine - just pointing out a different solution for times when you don't want to use a pivot table.
2Young2BBald
Footbaldguy
Thanks again for the help guys!!! In the process I also discovered SLICERS. I've never felt so alive...
Here's an accounting Excel question...
So we get a data dump from an inventory system that we would like to put into an Excel document and create a template to upload to our accounting software. From the inventory data dump we would like to be able to convert certain line items into many, but some will stay one to one. For example:
Inventory #3 - $100 - Property 10 will remain the same (because Property 10 does not get allocated)
Inventory #3 - $100 - Property 7 becomes the following line items:
Inventory #3 - $50 - Property 7.1
Inventory #3 - $50 - Property 7.2
Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.
Thanks.
So we get a data dump from an inventory system that we would like to put into an Excel document and create a template to upload to our accounting software. From the inventory data dump we would like to be able to convert certain line items into many, but some will stay one to one. For example:
Inventory #3 - $100 - Property 10 will remain the same (because Property 10 does not get allocated)
Inventory #3 - $100 - Property 7 becomes the following line items:
Inventory #3 - $50 - Property 7.1
Inventory #3 - $50 - Property 7.2
Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.
Thanks.
Ned
Footballguy
This is better off in Access, but it's doable in Excel if you have to do it there.
You first need to be able to tell what properties are supposed to be allocated as well as the number of allocations. Ideally, it's a static table that you can store in a separate tab where you'll be able to lookup the property ID and get the allocations. Then put the data dump into a separate tab and have the third tab calculate the results. If it were me, I'd macro it, but there's probably a (complicated) way to do it with formulas.
Spin
Footballguy
ragincajun
Footballguy
How did I miss this!!!
I will come back to read through but in the meantime....
FUZZY LOOKUP IS A THING
I will come back to read through but in the meantime....
FUZZY LOOKUP IS A THING
Long Ball Larry
Footballguy
Using solver, if I want to pull from a specific list of values, how would I do that?
Specifically, I'm trying to choose an optimal Daily Fantasy Baseball Lineup based on the player projections for the day. (I realize that it ultimately may not be the best way to do it, but I want to play around with it.
Specifically, I'm trying to choose an optimal Daily Fantasy Baseball Lineup based on the player projections for the day. (I realize that it ultimately may not be the best way to do it, but I want to play around with it.
Dinsy Ejotuz
Footballguy
Set up a binary column and use it as the decision variable ("by changing" cells) in Solver.
Last edited by a moderator:
Gawain
Footballguy
Do any of you Excel guys use third party software, such as BIME, Tableau, Qlikview or something else?
Does anyone have a recommendation?
While the visual representations offered by these companies are amazing, I need something more basic.
I'm looking for a way to upload raw sales data (either retail register sales or my sales to customers) into a static dashboard that will then create reports automatically and send them off to the proper folks (reorders for production, sales figures for execs, overstocks and movements for logistics).
The end output can be in excel or in a proprietary file, but it needs to be able to be manipulated.
Does anyone have a recommendation?
While the visual representations offered by these companies are amazing, I need something more basic.
I'm looking for a way to upload raw sales data (either retail register sales or my sales to customers) into a static dashboard that will then create reports automatically and send them off to the proper folks (reorders for production, sales figures for execs, overstocks and movements for logistics).
The end output can be in excel or in a proprietary file, but it needs to be able to be manipulated.
Similar threads
