Official Excel Help Corner

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

Ned · 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 #NA
For 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)

Spin · 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 #NA
For 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,"")

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

:goodposting:

Ahh, dunno how I missed this one. This is the correct answer here.

Herc · 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 #NA
For 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.

Herc · 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

Spin · 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 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

cap'n grunge · 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 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

Just use HLOOKUP like i posted. No need for all of this.

Spin · 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 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
Just 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.

Ned · 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 #NA
For 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. :doh:

cap'n grunge · 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 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
Just 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.

John Bender · 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 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).

cap'n grunge · 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.

John Bender · 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).

Spin · 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 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).

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 · March 18, 2015

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

Spin · 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.

Ned · 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.

Spin · 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.

Ned · 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. :shrug:

TheWalkmen · 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?

Ned · 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.

Corporation · March 25, 2015

Dot for later

TheWalkmen · 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.

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

Gawain · 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?

hagmania · March 26, 2015

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

Edited March 26, 2015 by hagmania

hagmania · March 26, 2015

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.

Edited March 26, 2015 by hagmania

Gawain · 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      AKA

How 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 · 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      AKA
How 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

2Young2BBald · 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.

Ned · 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".

2Young2BBald · 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.

Edited March 30, 2015 by 2Young2BBald

Dinsy Ejotuz · March 30, 2015

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

Ned · 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")

2Young2BBald · 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 :nerd:

acarey50 · 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.

bryhamm · 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.

bushdocda · 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.

2Young2BBald · 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.

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

Brony · 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.

2Young2BBald · March 31, 2015

Thanks again for the help guys!!! In the process I also discovered SLICERS. I've never felt so alive...

Normie32r · 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.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 · 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.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.

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.

Normie32r · April 23, 2015

Thanks. Do you offer Access help also???

Spin · 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.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.

Is property 7 always split? What determines if an item is going to be split or not?

cphk96 · April 23, 2015

Good stuff. Will return when I have more time to read through. :blackdot:

ragincajun · April 23, 2015

How did I miss this!!!

I will come back to read through but in the meantime....

FUZZY LOOKUP IS A THING

Long Ball Larry · 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.

Dinsy Ejotuz · June 3, 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.

Set up a binary column and use it as the decision variable ("by changing" cells) in Solver.

Edited June 3, 2015 by wdcrob

Gawain · June 9, 2015

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.

***Official*** Excel Help Corner

Recommended Posts

hagmania 9,034

Link to post

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Ned 10,758

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

Herc 10

Link to post

Share on other sites

Herc 10

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

cap'n grunge 8,096

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

Ned 10,758

Link to post

Share on other sites

cap'n grunge 8,096

Link to post

Share on other sites

John Bender 6,527

Link to post

Share on other sites

cap'n grunge 8,096

Link to post

Share on other sites

John Bender 6,527

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

Dinsy Ejotuz 13,801

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

Ned 10,758

Link to post

Share on other sites

Spin 1,014

Link to post

Share on other sites

Ned 10,758

Link to post

Share on other sites

TheWalkmen 312

Link to post

Share on other sites

Ned 10,758

Link to post

Share on other sites

Corporation 1,612

Link to post

Share on other sites

TheWalkmen 312

Link to post

Share on other sites

Gawain 2,372

Official Excel Help Corner