***Official*** Excel Help Corner

[Mr. Ected 2,899]

:sehorn:

[Herc 10]

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?

[hagmania 8,861]

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 10,583]

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 1,014]

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 1,014]

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.

[Herc 10]

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 10]

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 1,014]

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 7,631]

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 1,014]

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 10,583]

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.

[cap'n grunge 7,631]

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 6,527]

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 7,631]

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 6,527]

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 1,014]

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 13,018]

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 1,014]

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 10,583]

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 1,014]

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 10,583]

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.

[TheWalkmen 309]

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 10,583]

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 1,337]

Dot for later

[TheWalkmen 309]

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 2,249]

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 8,861]

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 8,861]

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 2,249]

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 14,318]

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 530]

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 10,583]

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 530]

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 13,018]

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 10,583]

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 530]

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

[acarey50 604]

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 872]

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 3,479]

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 530]

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 7,613]

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 530]

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

[Normie32r 2]

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 10,583]

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 2]

Thanks. Do you offer Access help also???

[Spin 1,014]

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 4]

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

[ragincajun 904]

How did I miss this!!!

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

FUZZY LOOKUP IS A THING

[Long Ball Larry 14,294]

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.