Mr. Ected 2,899 Posted March 17, 2015 Share Posted March 17, 2015 :sehorn: Quote Link to post Share on other sites
Herc 10 Posted March 17, 2015 Share Posted March 17, 2015 Looking for a little assistance please.=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))this line works great if there is info to find if not it returns an #NAFor some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong? Quote Link to post Share on other sites
hagmania 8,861 Posted March 17, 2015 Share Posted March 17, 2015 I like IFERROR a lot more than if(isna=IFERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)),"") 1 Quote Link to post Share on other sites
Ned 10,583 Posted March 17, 2015 Author Share Posted March 17, 2015 Looking for a little assistance please.=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))this line works great if there is info to find if not it returns an #NAFor some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?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) Quote Link to post Share on other sites
Spin 1,014 Posted March 17, 2015 Share Posted March 17, 2015 Looking for a little assistance please.=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))this line works great if there is info to find if not it returns an #NAFor some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?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)Just use IFERROR.=iferror(index/match,"") Quote Link to post Share on other sites
Spin 1,014 Posted March 17, 2015 Share Posted March 17, 2015 I like IFERROR a lot more than if(isna=IFERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)),"")Ahh, dunno how I missed this one. This is the correct answer here. Quote Link to post Share on other sites
Herc 10 Posted March 17, 2015 Share Posted March 17, 2015 Looking for a little assistance please.=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))this line works great if there is info to find if not it returns an #NAFor some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?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)Just use IFERROR.=iferror(index/match,"")Going to use this its much less typing intensive, thanks. Quote Link to post Share on other sites
Herc 10 Posted March 17, 2015 Share Posted March 17, 2015 I like IFERROR a lot more than if(isna=IFERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)),"")Thanks Quote Link to post Share on other sites
Spin 1,014 Posted March 17, 2015 Share Posted March 17, 2015 I feel like this should be really easy but I just cant figure it out.I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.For example:===|===A===|===B===|===C===|===D===|=1=|BLA====|=======|BLA====|=======|=2=|IAII=====|IAlI=====|=======|=======|=3=|12A====|=======|=======|12A====|Here's a solution that will handle just numbers as well as letters.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 StringDim N As LongDim M As LongDim R As RangeDim NumDims As LongDim LB As LongDim IsArrayAlloc As Boolean '''''''''''''''''''''''''''''''''''''''''''' If no parameters were passed in, return' vbNullString.'''''''''''''''''''''''''''''''''''''''''''If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit FunctionEnd 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 IfContinueLoop:Next N '''''''''''''''''''''''''''''' Remove the trailing Sep'''''''''''''''''''''''''''''If Len(Sep) > 0 Then If Len(S) > 0 Then S = Left(S, Len(S) - Len(Sep)) End IfEnd If StringConcat = S'''''''''''''''''''''''''''''' Success. Get out.'''''''''''''''''''''''''''''Exit FunctionErrH:'''''''''''''''''''''''''''''' 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 Quote Link to post Share on other sites
cap'n grunge 7,631 Posted March 17, 2015 Share Posted March 17, 2015 I feel like this should be really easy but I just cant figure it out.I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.For example:===|===A===|===B===|===C===|===D===|=1=|BLA====|=======|BLA====|=======|=2=|IAII=====|IAlI=====|=======|=======|=3=|12A====|=======|=======|12A====| Here's a solution that will handle just numbers as well as letters.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 StringDim N As LongDim M As LongDim R As RangeDim NumDims As LongDim LB As LongDim IsArrayAlloc As Boolean'''''''''''''''''''''''''''''''''''''''''''' If no parameters were passed in, return' vbNullString.'''''''''''''''''''''''''''''''''''''''''''If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit FunctionEnd IfFor 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 IfContinueLoop:Next N'''''''''''''''''''''''''''''' Remove the trailing Sep'''''''''''''''''''''''''''''If Len(Sep) > 0 Then If Len(S) > 0 Then S = Left(S, Len(S) - Len(Sep)) End IfEnd IfStringConcat = S'''''''''''''''''''''''''''''' Success. Get out.'''''''''''''''''''''''''''''Exit FunctionErrH:'''''''''''''''''''''''''''''' Error. Return #VALUE'''''''''''''''''''''''''''''StringConcat = CVErr(xlErrValue)End FunctionJust 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 / etcJust use HLOOKUP like i posted. No need for all of this. Quote Link to post Share on other sites
Spin 1,014 Posted March 17, 2015 Share Posted March 17, 2015 I feel like this should be really easy but I just cant figure it out.I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.For example:===|===A===|===B===|===C===|===D===|=1=|BLA====|=======|BLA====|=======|=2=|IAII=====|IAlI=====|=======|=======|=3=|12A====|=======|=======|12A====| Here's a solution that will handle just numbers as well as letters.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 StringDim N As LongDim M As LongDim R As RangeDim NumDims As LongDim LB As LongDim IsArrayAlloc As Boolean'''''''''''''''''''''''''''''''''''''''''''' If no parameters were passed in, return' vbNullString.'''''''''''''''''''''''''''''''''''''''''''If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit FunctionEnd IfFor 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 IfContinueLoop:Next N'''''''''''''''''''''''''''''' Remove the trailing Sep'''''''''''''''''''''''''''''If Len(Sep) > 0 Then If Len(S) > 0 Then S = Left(S, Len(S) - Len(Sep)) End IfEnd IfStringConcat = S'''''''''''''''''''''''''''''' Success. Get out.'''''''''''''''''''''''''''''Exit FunctionErrH:'''''''''''''''''''''''''''''' Error. Return #VALUE'''''''''''''''''''''''''''''StringConcat = CVErr(xlErrValue)End FunctionJust 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 / etcJust use HLOOKUP like i posted. No need for all of this.I don't think the wildcard handles integers, does it? Not sure if this is even a scenario to worry about in this case though. Quote Link to post Share on other sites
Ned 10,583 Posted March 17, 2015 Author Share Posted March 17, 2015 Looking for a little assistance please.=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))this line works great if there is info to find if not it returns an #NAFor some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?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)Just use IFERROR.=iferror(index/match,"")Going to use this its much less typing intensive, thanks.Ugh, and here I thought IFERROR only returned a result if there was an error. Quote Link to post Share on other sites
cap'n grunge 7,631 Posted March 17, 2015 Share Posted March 17, 2015 I feel like this should be really easy but I just cant figure it out.I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.For example:===|===A===|===B===|===C===|===D===|=1=|BLA====|=======|BLA====|=======|=2=|IAII=====|IAlI=====|=======|=======|=3=|12A====|=======|=======|12A====| Here's a solution that will handle just numbers as well as letters.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 StringDim N As LongDim M As LongDim R As RangeDim NumDims As LongDim LB As LongDim IsArrayAlloc As Boolean'''''''''''''''''''''''''''''''''''''''''''' If no parameters were passed in, return' vbNullString.'''''''''''''''''''''''''''''''''''''''''''If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit FunctionEnd IfFor 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 IfContinueLoop:Next N'''''''''''''''''''''''''''''' Remove the trailing Sep'''''''''''''''''''''''''''''If Len(Sep) > 0 Then If Len(S) > 0 Then S = Left(S, Len(S) - Len(Sep)) End IfEnd IfStringConcat = S'''''''''''''''''''''''''''''' Success. Get out.'''''''''''''''''''''''''''''Exit FunctionErrH:'''''''''''''''''''''''''''''' Error. Return #VALUE'''''''''''''''''''''''''''''StringConcat = CVErr(xlErrValue)End FunctionJust 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 / etcJust use HLOOKUP like i posted. No need for all of this. I don't think the wildcard handles integers, does it? Not sure if this is even a scenario to worry about in this case though.You're right. Quote Link to post Share on other sites
John Bender 6,527 Posted March 17, 2015 Share Posted March 17, 2015 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 categoriesSo 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). Quote Link to post Share on other sites
cap'n grunge 7,631 Posted March 17, 2015 Share Posted March 17, 2015 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. Quote Link to post Share on other sites
John Bender 6,527 Posted March 17, 2015 Share Posted March 17, 2015 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.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). Quote Link to post Share on other sites
Spin 1,014 Posted March 17, 2015 Share Posted March 17, 2015 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 categoriesSo 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). 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. Quote Link to post Share on other sites
Dinsy Ejotuz 13,018 Posted March 18, 2015 Share Posted March 18, 2015 (edited) 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). Edited March 18, 2015 by wdcrob Quote Link to post Share on other sites
Spin 1,014 Posted March 25, 2015 Share Posted March 25, 2015 Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")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. I know my other one worksjust fine, but it's more just bugging me at this point. Quote Link to post Share on other sites
Ned 10,583 Posted March 25, 2015 Author Share Posted March 25, 2015 Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")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. I know my other one worksjust fine, but it's more just bugging me at this point.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. Quote Link to post Share on other sites
Spin 1,014 Posted March 25, 2015 Share Posted March 25, 2015 Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")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. I know my other one worksjust fine, but it's more just bugging me at this point.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. 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. Quote Link to post Share on other sites
Ned 10,583 Posted March 25, 2015 Author Share Posted March 25, 2015 Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")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. I know my other one worksjust fine, but it's more just bugging me at this point.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. 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.I think what you did is best. The only other way is to enter a ton of specific criteria in AVERAGEIFS. Quote Link to post Share on other sites
TheWalkmen 309 Posted March 25, 2015 Share Posted March 25, 2015 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? Quote Link to post Share on other sites
Ned 10,583 Posted March 25, 2015 Author Share Posted March 25, 2015 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? 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. Quote Link to post Share on other sites
Corporation 1,337 Posted March 25, 2015 Share Posted March 25, 2015 Dot for later Quote Link to post Share on other sites
TheWalkmen 309 Posted March 25, 2015 Share Posted March 25, 2015 (edited) 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? 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.Yeah I use F4 to lock the formulas, but it only works on 1 cell at a time, can I lock multiple cells at the same time? Edited March 25, 2015 by TheWalkmen Quote Link to post Share on other sites
Gawain 2,249 Posted March 26, 2015 Share Posted March 26, 2015 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? Quote Link to post Share on other sites
hagmania 8,861 Posted March 26, 2015 Share Posted March 26, 2015 (edited) 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 exampleColumn B = 1 if qualifies to be in Column A of the table in your example, 0 if notColumn C = 1 if qualifies to be in Column B of the table in your example, 0 if not Edited March 26, 2015 by hagmania Quote Link to post Share on other sites
hagmania 8,861 Posted March 26, 2015 Share Posted March 26, 2015 (edited) 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 C2) Remove Duplicates from Column C and Sort Column C alphabetically3) D2. =IFERROR(VLOOKUP(C2, A:A, 1, 0), "") copy down4) E2. =IFERROR(VLOOKUP(C2, B:B, 1, 0), "") copy down5) Copy/Paste values in D and E6) Columns D and E are now your properly sorted/matched Columns A and B. Edited March 26, 2015 by hagmania Quote Link to post Share on other sites
Gawain 2,249 Posted March 26, 2015 Share Posted March 26, 2015 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) Quote Link to post Share on other sites
shadyridr 14,318 Posted March 27, 2015 Share Posted March 27, 2015 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)use access Quote Link to post Share on other sites
2Young2BBald 530 Posted March 30, 2015 Share Posted March 30, 2015 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. Quote Link to post Share on other sites
Ned 10,583 Posted March 30, 2015 Author Share Posted March 30, 2015 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.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". Quote Link to post Share on other sites
2Young2BBald 530 Posted March 30, 2015 Share Posted March 30, 2015 (edited) 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.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".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. Edited March 30, 2015 by 2Young2BBald Quote Link to post Share on other sites
Dinsy Ejotuz 13,018 Posted March 30, 2015 Share Posted March 30, 2015 (edited) 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. Edited March 31, 2015 by wdcrob Quote Link to post Share on other sites
Ned 10,583 Posted March 30, 2015 Author Share Posted March 30, 2015 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.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".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. Ooops, I completely overlooked that small detail!=TEXT(A2,"mmm-yy") Quote Link to post Share on other sites
2Young2BBald 530 Posted March 30, 2015 Share Posted March 30, 2015 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.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".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. Ooops, I completely overlooked that small detail!=TEXT(A2,"mmm-yy")You ####### ROCK Quote Link to post Share on other sites
acarey50 604 Posted March 30, 2015 Share Posted March 30, 2015 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.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. Quote Link to post Share on other sites
bryhamm 872 Posted March 31, 2015 Share Posted March 31, 2015 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. 1 Quote Link to post Share on other sites
bushdocda 3,479 Posted March 31, 2015 Share Posted March 31, 2015 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. 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.This is my answer as well. Quote Link to post Share on other sites
2Young2BBald 530 Posted March 31, 2015 Share Posted March 31, 2015 (edited) 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. 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.This is my answer as well.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. Edited March 31, 2015 by 2Young2BBald Quote Link to post Share on other sites
Brony 7,613 Posted March 31, 2015 Share Posted March 31, 2015 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. Quote Link to post Share on other sites
2Young2BBald 530 Posted March 31, 2015 Share Posted March 31, 2015 Thanks again for the help guys!!! In the process I also discovered SLICERS. I've never felt so alive... Quote Link to post Share on other sites
Normie32r 2 Posted April 23, 2015 Share Posted April 23, 2015 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.1Inventory #3 - $50 - Property 7.2Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.Thanks. Quote Link to post Share on other sites
Ned 10,583 Posted April 23, 2015 Author Share Posted April 23, 2015 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.1Inventory #3 - $50 - Property 7.2Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.Thanks.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. Quote Link to post Share on other sites
Normie32r 2 Posted April 23, 2015 Share Posted April 23, 2015 Thanks. Do you offer Access help also??? Quote Link to post Share on other sites
Spin 1,014 Posted April 23, 2015 Share Posted April 23, 2015 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.1Inventory #3 - $50 - Property 7.2Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.Thanks.Is property 7 always split? What determines if an item is going to be split or not? Quote Link to post Share on other sites
cphk96 4 Posted April 23, 2015 Share Posted April 23, 2015 Good stuff. Will return when I have more time to read through. Quote Link to post Share on other sites
ragincajun 904 Posted April 23, 2015 Share Posted April 23, 2015 How did I miss this!!!I will come back to read through but in the meantime....FUZZY LOOKUP IS A THING Quote Link to post Share on other sites
Long Ball Larry 14,294 Posted June 2, 2015 Share Posted June 2, 2015 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. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.