Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

13 minutes ago, roadkill1292 said:

Is it too simple to suggest doing Move or Copy? Right click on the tab and you can place it wherever you want.

Every time I try to answer one of these help questions I find out I'm actually years behind what everybody else already knows and assumes. I have no idea where your Excel skills are, gb, just trying to help.

No, that's exactly what I needed to do. Been using Excel for a million years and it somehow never came up for me before then. Anyway, someone showed me the light, but I appreciate you responding GB.

Share this post


Link to post
Share on other sites

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

Share this post


Link to post
Share on other sites
6 minutes ago, Long Ball Larry said:

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

Including duplicates?

Share this post


Link to post
Share on other sites
41 minutes ago, Long Ball Larry said:

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

In column 61..... =CONCATENATE(your cells) and drag down through the last row. 

Share this post


Link to post
Share on other sites
3 minutes ago, Ned said:

In column 61..... =CONCATENATE(your cells) and drag down through the last row. 

Won't that keep them in their current rows?  I need to stack them all on top of each other in 1 column.

Share this post


Link to post
Share on other sites
21 minutes ago, Long Ball Larry said:

Won't that keep them in their current rows?  I need to stack them all on top of each other in 1 column.

Yeah they'd all be in one column by 500 rows.....A B C ABC

What do you mean by "stack them all on top of each other in one column"?

Edited by Ned

Share this post


Link to post
Share on other sites
Just now, Ned said:

Yeah they'd all be in one column by 500 rows.....A B C ABC

What do you mean by "stack them all on top of each other in one column"?

START

Column A               Column B             Column C

Dog                    Eagle                Tiger

Cat                    Fish                 Lion

Bear                                        Muskrat

Weasel


RESULT

Column A

Dog
Cat
Bear
Weasel
Eagle
Fish
Tiger
Lion
Muskrat

 

Share this post


Link to post
Share on other sites
2 minutes ago, Ned said:

Yeah they'd all be in one column by 500 rows.....A B C ABC

What do you mean by "stack them all on top of each other in one column"?

I think he wants a list of all the values from each of the columns in a single column.  Like a master list of values.  Not all the values for each column concatenated together.

Share this post


Link to post
Share on other sites
1 hour ago, Long Ball Larry said:

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

Do you want a static list or dynamic?  If static, just copy and paste the values from each of the 60 columns into a single column, resulting in one column with about 3000 rows.  Then, if you want to remove the duplicates, you can highlight the column, click on the Data menu and then use the Remove Duplicates function.

If you want a dynamic list and you don't mind duplicates, you can create a column that has as its first 500 rows a reference to the same cell in column A.  So for the first row, it'd be =A1, the second row would be =A2, etc. down to =A500.  Then, for row 501, you put =B1, do the same as for the A values, and repeat for all 60 columns.

Share this post


Link to post
Share on other sites
Just now, bcdjr1 said:

Do you want a static list or dynamic?  If static, just copy and paste the values from each of the 60 columns into a single column, resulting in one column with about 3000 rows.  Then, if you want to remove the duplicates, you can highlight the column, click on the Data menu and then use the Remove Duplicates function.

If you want a dynamic list and you don't mind duplicates, you can create a column that has as its first 500 rows a reference to the same cell in column A.  So for the first row, it'd be =A1, the second row would be =A2, etc. down to =A500.  Then, for row 501, you put =B1, do the same as for the A values, and repeat for all 60 columns.

static, and yes, I realize that it could be cut and pasted, but I figured there should be a faster way.

Share this post


Link to post
Share on other sites
Just now, Long Ball Larry said:

static, and yes, I realize that it could be cut and pasted, but I figured there should be a faster way.

That's pretty fast.  I could do it in >5 minutes.

 

Share this post


Link to post
Share on other sites

 

51 minutes ago, bcdjr1 said:

Do you want a static list or dynamic?  If static, just copy and paste the values from each of the 60 columns into a single column, resulting in one column with about 3000 rows.  Then, if you want to remove the duplicates, you can highlight the column, click on the Data menu and then use the Remove Duplicates function.

If you want a dynamic list and you don't mind duplicates, you can create a column that has as its first 500 rows a reference to the same cell in column A.  So for the first row, it'd be =A1, the second row would be =A2, etc. down to =A500.  Then, for row 501, you put =B1, do the same as for the A values, and repeat for all 60 columns.

This is the only way I can see doing it.  To remove dups, highlight your column and use the 'Remove Duplicates' function in the Data ribbon.

Otherwise, you'd need to write a macro.

 

 

Share this post


Link to post
Share on other sites

Try this... It assumes you have headers in each column and want your new data starting in row 2.  I didn't test it, so YMMV.  Afterwards, use the Remove Duplicates function.

Sub Larry()

Dim LarryRng As Range
Set LarryRng = Range(Range("A1"), Range("A1").End(xlToRight))

Dim LastCol, LastCopyRow, LastPasteRow
LastCol = Range("A1").End(xlToRight).Column
LastPasteRow = 2

For Each cell In LarryRng
    LastCopyRow = Cells(1048576, cell.Column).End(xlUp).Row
    Range(Cells(LastPasteRow, LastCol + 1), Cells(LastCopyRow + LastPasteRow - 2, LastCol + 1)).Value = _
        Range(Cells(2, cell.Column), Cells(LastCopyRow, cell.Column)).Value
    LastPasteRow = Cells(1048576, LastCol + 1).End(xlUp).Row + 1
Next
    
End Sub

 

Share this post


Link to post
Share on other sites

Hello,

2 questions:

Question 1:

I have a formula in Column AZ, multiplying 4 fields together to create a value.

=[@[A]]*([@]*[@[C]]*[@[D]])

I want to add a date check to this date.

Column AR is a date field (ex. of field: 26-May-16)

If Column AR is not TODAY (05/25/16) OR the date is GT 2 business days into the future, then I want the above formula to be equal to a 1, else calculate the field normally. Blank values in Column AR also need to be accounted for and should default to the actual calculation/value in AZ.

*******

2) Conditional Formatting using other fields in my view - I want to conditionally highlight/format the above formula based on the below.

Column B:
Same-As
New
Modified

Column C:
Standard
Moderate
Complex

IF Column AB (Order Assigned Date (ex. 22-May-16) 

If Column B is = Same-As AND Column C is = Standard AND the difference between TODAY's date (including only business days) and Column AB is GT 2 business days, then highlight Column AZ with a bold red.
If Column B is = New AND Column C is = Standard AND the difference between TODAY's date (including only business days) and Column AB is GT 4 business days, then highlight Column AZ with a bold red.
If Column B is = Modified AND Column C is = Standard AND the difference between TODAY's date (including only business days) and Column AB is GT 4 business days, then highlight Column AZ with a bold red.

IF Column AB (Order Assigned Date (ex. 22-May-16) is

Thanks!

Share this post


Link to post
Share on other sites

@cubd8 here's #1, where AR2 is your date cell.

=IF(OR(TODAY()=AR2,NETWORKDAYS(TODAY(),AR2)=2),1,"your formula here")

#2 is more complicated; You'd need to nest OR statements within AND statements.  Use the NETWORKDAYS statement to get business day diffs.  TODAY() always returns today's date based on your system settings.

Share this post


Link to post
Share on other sites
3 hours ago, Ned said:

@cubd8 here's #1, where AR2 is your date cell.

=IF(OR(TODAY()=AR2,NETWORKDAYS(TODAY(),AR2)=2),1,"your formula here")

#2 is more complicated; You'd need to nest OR statements within AND statements.  Use the NETWORKDAYS statement to get business day diffs.  TODAY() always returns today's date based on your system settings.

Sorry - I just realized that I was given incomplete information (the bold is not intended to be bold below, my font is messed up):

I have a formula in Column AZ, multiplying 4 fields together to create a value.

=[@[A]]*([@B]*[@[C]]*[@[D]])
 

If Column AR is GT 2 business days (past) from TODAY, then I want the formula to be equal to a 1 .  Blank values in Column AR also need to be accounted for and should default to the actual calculation/value in AZ.

Example - if Today is 05/25/2016 and Column AR = 05/22 (Assuming all are business days), then this formula should be set to 1.
Example - if Today is 05/25/2016 and Column AR = 05/24 (Assuming all are business days), then output the formula.
Example - if Today is 05/25/2016 and Column AR = 05/23 (Assuming all are business days), then output the formula.

Share this post


Link to post
Share on other sites

 

17 hours ago, Long Ball Larry said:

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

This is not ideal and might freeze up your Excel if your data set was much larger but I think should work for this and is faster and less labor intensive than copy/pasting 59 times.  In A501 (or basically one cell below the longest list in your set) put =B1.  Drag this right under your 60 columns.  Then drag them all down to row 30,000.  Now you have what you want in column A.  Copy/paste as values, and then delete the other 59 columns.  There will be blanks in there from the lists that were shorter than the max length, but you can sort or otherwise filter them out. :shrug:

Share this post


Link to post
Share on other sites
17 hours ago, Long Ball Larry said:

START

Column A               Column B             Column C

Dog                    Eagle                Tiger

Cat                    Fish                 Lion

Bear                                        Muskrat

Weasel


RESULT

Column A

Dog
Cat
Bear
Weasel
Eagle
Fish
Tiger
Lion
Muskrat

 

 

There's a way to do it with a formula, but it sucks:

 

Click and drag to select everything in Column A. Go to Insert>Name>Define... and in the box type List1 for the range $A$1:$A$4

Repeat for Column B, Insert>Name>Define... List2 for $B$1:$B$2

Same for Column C, List3 for $C$1:$C$3

 

In D1, type

=IFERROR(INDEX(List1,ROWS(D1:$D$1)),IFERROR(INDEX(List2,ROWS(D1:$D$1)-ROWS(List1)),IFERROR(INDEX(List3,ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)),"")))

It'll say Dog

Click on the cell. Hover over the lower right corner until it forms a black +

Click-and-Drag all the way down column D.

 

This will be incredibly tedious with 60 columns, though. Sorry man.

Share this post


Link to post
Share on other sites
2 hours ago, cubd8 said:

Sorry - I just realized that I was given incomplete information (the bold is not intended to be bold below, my font is messed up):

I have a formula in Column AZ, multiplying 4 fields together to create a value.

=[@[A]]*([@B]*[@[C]]*[@[D]])
 

If Column AR is GT 2 business days (past) from TODAY, then I want the formula to be equal to a 1 .  Blank values in Column AR also need to be accounted for and should default to the actual calculation/value in AZ.

Example - if Today is 05/25/2016 and Column AR = 05/22 (Assuming all are business days), then this formula should be set to 1.
Example - if Today is 05/25/2016 and Column AR = 05/24 (Assuming all are business days), then output the formula.
Example - if Today is 05/25/2016 and Column AR = 05/23 (Assuming all are business days), then output the formula.

=IF(ISBLANK(AR2),"your formula here",IF((NETWORKDAYS(AR2,TODAY()-1))>2,1,"your formula here"))

Share this post


Link to post
Share on other sites
6 hours ago, Ned said:

=IF(ISBLANK(AR2),"your formula here",IF((NETWORKDAYS(AR2,TODAY()-1))>2,1,"your formula here"))

I tried inserting that into my formula, not working:

=IF(ISBLANK(AR2),"[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])",IF((NETWORKDAYS(AR2,TODAY()-1))>2,1,"[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])"))

Share this post


Link to post
Share on other sites
8 hours ago, cubd8 said:

I tried inserting that into my formula, not working:

=IF(ISBLANK(AR2),"[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])",IF((NETWORKDAYS(AR2,TODAY()-1))>2,1,"[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])"))

The "" shouldn't be around your formula.  Otherwise...need to know what "not working" means.  Are you getting an error?

Share this post


Link to post
Share on other sites
On 5/26/2016 at 5:50 AM, Ned said:

The "" shouldn't be around your formula.  Otherwise...need to know what "not working" means.  Are you getting an error?

So, I modified some to try this - I have additional 'IF/AND' statements to declare, but trying to get this to work with 2 statements up-top. In the below formula, the last '0' is the answer when none of the conditions are true, but if I want to this, I would essentially repeat the the '1', following by the next IF/AND statements (I will have 15 or so of these), but having a hard time getting the syntax down for 2.

My current error - 

When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula. 

This is what I'm trying to say -  IF Setup  Description = As-Is (No Changes) AND Complexity = Standard AND Days In Review GT 2 Business Days, then output a 1, else IF Setup Description = New and Complexity = Standard and GT 3 Days in Review, output a 1, else a 0.

My current code with error:

=IF(AND([@[Setup Description]]= "As-Is (No Changes)",[@Complexity] = "Standard",[@['#Days In Review]]>2,1,IF(AND([@[Setup Description]]= "New",[@Complexity] = "Standard",[@['#Days In Review]]>3,1,0))

Share this post


Link to post
Share on other sites
23 hours ago, cubd8 said:

So, I modified some to try this - I have additional 'IF/AND' statements to declare, but trying to get this to work with 2 statements up-top. In the below formula, the last '0' is the answer when none of the conditions are true, but if I want to this, I would essentially repeat the the '1', following by the next IF/AND statements (I will have 15 or so of these), but having a hard time getting the syntax down for 2.

My current error - 

When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula. 

This is what I'm trying to say -  IF Setup  Description = As-Is (No Changes) AND Complexity = Standard AND Days In Review GT 2 Business Days, then output a 1, else IF Setup Description = New and Complexity = Standard and GT 3 Days in Review, output a 1, else a 0.

My current code with error:

=IF(AND([@[Setup Description]]= "As-Is (No Changes)",[@Complexity] = "Standard",[@['#Days In Review]]>2),1,IF(AND([@[Setup Description]]= "New",[@Complexity] = "Standard",[@['#Days In Review]]>3),1,0))

You're missing parenthesis to close up the AND statements.

Share this post


Link to post
Share on other sites
On 6/14/2016 at 4:40 PM, Soootch said:

It's not pretty, but I gave you an idea on how to tackle combining the race picks using the table in columns R:AJ.

https://drive.google.com/file/d/0BwJEvQwzfDRZeEFxR0c0QWZRT1k/view?usp=sharing

I can help with the CSV, but need more info/example that matches the file.

Share this post


Link to post
Share on other sites

Need some VBA help...

If sheet 1 cell A8 has data, copy B8 and paste to sheet 2 in row 1

Then check sheet 1 cell A19 and if it has data, copy and paste B19 to sheet 2 in row 2

Then check sheet 1 cell A30 and repeat on down to cell A217 (there's an 11 row offset each time we check).  If our check cell is blank at any point, stop.

I can't figure out how to construct the loop... I've got all the copying down for the first iteration and can get it to paste into the next blank row just need help with the loop.

Hopefully this makes some sense.

Share this post


Link to post
Share on other sites
3 minutes ago, Ignoramus said:

Need some VBA help...

If sheet 1 cell A8 has data, copy B8 and paste to sheet 2 in row 1

Then check sheet 1 cell A19 and if it has data, copy and paste B19 to sheet 2 in row 2

Then check sheet 1 cell A30 and repeat on down to cell A217 (there's an 11 row offset each time we check).  If our check cell is blank at any point, stop.

I can't figure out how to construct the loop... I've got all the copying down for the first iteration and can get it to paste into the next blank row just need help with the loop.

Hopefully this makes some sense.

Not necessarily a VBA guy, but sounds like it's a for loop for your iterations, with a nested while loop.  While (checkcell != blank (or whatever the empty check is for VBA))..  Then you just keep setting that checkcell variable to what you need to check, if it ever becomes blank it exists the while loop and moves on.

  • Like 1

Share this post


Link to post
Share on other sites

Wrap a For/Each loop around your code.  Wrote this from iPhone so no guarantees it's perfect...

For x=8 to 217

With Sheets("Sheet1").cells(X,1)

if .value = vbnullstring then

Msgbox "ooops blank"

exit sub 'kills macro

else

.copy destination:= Sheets("Sheet2").cells(X,2)

end if

X=X+11

next X 

  • Like 1

Share this post


Link to post
Share on other sites
1 hour ago, Ned said:

Wrap a For/Each loop around your code.  Wrote this from iPhone so no guarantees it's perfect...

For x=8 to 217

With Sheets("Sheet1").cells(X,1)

if .value = vbnullstring then

Msgbox "ooops blank"

exit sub 'kills macro

else

.copy destination:= Sheets("Sheet2").cells(X,2)

end if

X=X+11

next X 

Thanks!  Will try this. 

Share this post


Link to post
Share on other sites
1 hour ago, Ignoramus said:

Thanks!  Will try this. 

 

3 hours ago, Ned said:

Wrap a For/Each loop around your code.  Wrote this from iPhone so no guarantees it's perfect...

For x=8 to 217

With Sheets("Sheet1").cells(X,1)

if .value = vbnullstring then

Msgbox "ooops blank"

exit sub 'kills macro

else

.copy destination:= Sheets("Sheet2").cells(X,2)

end if

END WITH

X=X+11

next X 

Noticed there's an End With missing. 

  • Like 1

Share this post


Link to post
Share on other sites
12 hours ago, Ignoramus said:

Thanks!  Will try this. 

Now that I'm in front of a PC, I realized I didn't read your post fully.  This should work for you...

Sub Ignoramus()
    For x = 8 To 217
        If Sheets("Sheet1").Cells(x, 1) = vbNullString Then
            MsgBox Cells(x, 1).Address & " is blank!"
            Exit Sub 'kills macro
        Else
            If Sheets("Sheet2").Range("A1048576").End(xlUp) = vbNullString Then
                Sheets("Sheet1").Cells(x, 2).Copy Destination:=Sheets("Sheet2").Range("A1048576").End(xlUp)
            Else
                Sheets("Sheet1").Cells(x, 2).Copy Destination:=Sheets("Sheet2").Range("A1048576").End(xlUp).Offset(1, 0)
            End If
        End If
        x = x + 10 'increment by 10 since for/each will add 1 by default
    Next x
End Sub

 

  • Like 1

Share this post


Link to post
Share on other sites

Hello - I need assistance in calculating/adding on to some existing formula's in the attached spreadsheet.

Column's BR and BX use formula's to create scores based upon other columns. My goal is to override the score's in Column's BR AND BX when the Group Value has a specific value (using look up values from another sheet in the tab)

If Column 'BD' (Group) = TUIA49 or FP04 then I want to override the score in 'BR' (Associate Capacity Score) and BX (PROD Associate Capacity Score) with a value of 300.

I will be having additional 'Group's' (Column BD) added over time so I want this to be a look-up value in case I wanted to add others.

'BR' (Associate Capacity Score) = the columns that make us this score reference the 'Workload Look Up Table'. Columns Q / R provide the Group ID and the weight
'BY' (Associate Capacity Score) = the columns that make us this score reference the 'PROD Workload Look Up Table'. Columns T / U provide the Group ID and the weight.

I can provide a sample spreadsheet if someone can take a look??

BR Column formula: - need to add a check to look-up the groupID (column BD) in Tab: 'Workload Look Up Table' (Column Q for the  Group, and R for the Weight (300) 

=IF([@CSC]=[@Primary],0,IF(AND(NOT([@[Max Est Ship Date]]=""),[@[Max Est Ship Date]] < WORKDAY(TODAY(),-2)),1,[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])))

BX Column formula: - need to add a check to look-up the groupID (column BD) in Tab: 'PROD Workload Look Up Table' (Column T for the  Group, and U for the Weight (300) 

=IF(AC6=AD6,0,IF(AND(NOT(BA:BA=""),BA:BA<WORKDAY(TODAY(),-2)),1,IF(NOT(H:H="Analysis"),(BS:BS*(BT:BT*BU:BU*BV:BV*BW:BW)),((BS:BS*(BT:BT*BU:BU*BV:BV*BW:BW))+((BS:BS*(BT:BT*BU:BU*BV:BV*BW:BW)))*0.5))))

Share this post


Link to post
Share on other sites

A spreadsheet would be a big help in visualizing this.

Share this post


Link to post
Share on other sites
54 minutes ago, Ned said:

A spreadsheet would be a big help in visualizing this.

How do you attach spreadsheets ?

  • Like 1

Share this post


Link to post
Share on other sites
16 minutes ago, cubd8 said:

How do you attach spreadsheets ?

You'll need to put on the web somewhere (dropbox or google drive or something) where we can get it and put the link here.

Share this post


Link to post
Share on other sites
6 hours ago, Ned said:

Now that I'm in front of a PC, I realized I didn't read your post fully.  This should work for you...


Sub Ignoramus()
    For x = 8 To 217
        If Sheets("Sheet1").Cells(x, 1) = vbNullString Then
            MsgBox Cells(x, 1).Address & " is blank!"
            Exit Sub 'kills macro
        Else
            If Sheets("Sheet2").Range("A1048576").End(xlUp) = vbNullString Then
                Sheets("Sheet1").Cells(x, 2).Copy Destination:=Sheets("Sheet2").Range("A1048576").End(xlUp)
            Else
                Sheets("Sheet1").Cells(x, 2).Copy Destination:=Sheets("Sheet2").Range("A1048576").End(xlUp).Offset(1, 0)
            End If
        End If
        x = x + 10 'increment by 10 since for/each will add 1 by default
    Next x
End Sub

 

Thanks man.  The for/each worked like a charm.  I had the rest and it was dumbed down for my example but you got me over the hurdle.  Appreciate the help!

  • Like 1

Share this post


Link to post
Share on other sites
3 hours ago, bcdjr1 said:

You'll need to put on the web somewhere (dropbox or google drive or something) where we can get it and put the link here.

Any email addresses I can send a copy/sample too? Would that be easier?

 

Share this post


Link to post
Share on other sites
19 hours ago, cubd8 said:

Any email addresses I can send a copy/sample too? Would that be easier?

 

GDrive is super easy.  Just drop it there so multiple people can see it.

Share this post


Link to post
Share on other sites
5 minutes ago, Ned said:

GDrive is super easy.  Just drop it there so multiple people can see it.

Yeah, besides emailing adds too much pressure to produce results. If you put it out where anyone can get to it, I dont feel like I have to solve your problem if I don't feel like it, whereas if you emailed it to me, it'd feel like I've already agreed to something.

Share this post


Link to post
Share on other sites

Excel question (and I still need to try and give Google Drive a shot)...hopefully this question can be answered without the .xls though.

Currently, this is saying (beginning with the 2nd IF statment that if the G2 statement is New, then refer to a lookup table and calculate times the value of Column BX2, else multiple all other values by another table in the lookup. I need to add 2 other possibilities before the final statement.

Current Formula that I have:
=IF(OR(AD2=AE2,AE2="Assigned"),BX2,IF(G2="New",(BX2*'PROD Workload Look Up Table'!$X$2),(BX2*'PROD Workload Look Up Table'!$X$3)))

Need to account for this:
I need to account for G2 = "As-Is (No Changes)" and do the same calculation as "New" with the only difference to look at X3.
I need to account for G2 = "Modified" and the same calculation as "New" with the only difference to look at X4.
The last statment in the current forumla is fine (with the exception of now looking at X5 in the lookup table). This is the catch-all bucket.

How do I add to the current formula and account for the other values in Column G2?

 

 

Share this post


Link to post
Share on other sites
10 hours ago, cubd8 said:

Excel question (and I still need to try and give Google Drive a shot)...hopefully this question can be answered without the .xls though.

Currently, this is saying (beginning with the 2nd IF statment that if the G2 statement is New, then refer to a lookup table and calculate times the value of Column BX2, else multiple all other values by another table in the lookup. I need to add 2 other possibilities before the final statement.

Current Formula that I have:
=IF(OR(AD2=AE2,AE2="Assigned"),BX2,IF(G2="New",(BX2*'PROD Workload Look Up Table'!$X$2),(BX2*'PROD Workload Look Up Table'!$X$3)))

Need to account for this:
I need to account for G2 = "As-Is (No Changes)" and do the same calculation as "New" with the only difference to look at X3.
I need to account for G2 = "Modified" and the same calculation as "New" with the only difference to look at X4.
The last statment in the current forumla is fine (with the exception of now looking at X5 in the lookup table). This is the catch-all bucket.

How do I add to the current formula and account for the other values in Column G2?

Just add to your IF statement when you're checking the value of G2 = "New".  In the FALSE part of the statement, instead of (BX2*'PROD Workload Look Up Table'!$X$3), put in IF(G2="As-Is (No Changes)" then its true condition, and then in the false statement put IF(G2="Modified") then its true condition, and for the false condition, put the catch all.

If you care, this would be what they call nested if statements.

Edited by bcdjr1

Share this post


Link to post
Share on other sites

Here's another one:

I need to do a VLOOKU/HLOOKUP (or so I'm told) to capture the hours each employees works each week. I need the two most recent weeks and need to make the forumula re-freshable so that I'm always capturing the two most recent weeks.

I have a workbook tab called 'Associates' that contains names:

Example:

A2: Smith

A3: Jones

A4: Johnson

I have another workbook called '2016 Data' that contains a tab called '2016 - Manager'. That tab contains name and hours worked per week. Each week (it's on Week 26 currently), a new column is added to the right when the raw data is refreshed. Each name has a # of hours (ex. 34.5, 47, etc.) associated to each Name in A2. So, the data is added horizontally. For example, Week 25 is in column Z starting in column 5, and Week 26 is column AA, starting in Column 5. Each week a new column is added to the right.

Column A has names:

A2: Smith

A3: Jones

A4: Johnson

How would I compare the Names from the 'Associates' tab (beginning in A2) to the tab '2016 - Manager' within the workbook called '2016 Data' by Names (beginning in A2) and then returning the value of  the 2 more recent weeks (Week 25 in Column z and Week 26 in column AA) to Columns K and L.

Share this post


Link to post
Share on other sites
12 minutes ago, cubd8 said:

Here's another one:

I need to do a VLOOKU/HLOOKUP (or so I'm told) to capture the hours each employees works each week. I need the two most recent weeks and need to make the forumula re-freshable so that I'm always capturing the two most recent weeks.

I have a workbook tab called 'Associates' that contains names:

Example:

A2: Smith

A3: Jones

A4: Johnson

I have another workbook called '2016 Data' that contains a tab called '2016 - Manager'. That tab contains name and hours worked per week. Each week (it's on Week 26 currently), a new column is added to the right when the raw data is refreshed. Each name has a # of hours (ex. 34.5, 47, etc.) associated to each Name in A2. So, the data is added horizontally. For example, Week 25 is in column Z starting in column 5, and Week 26 is column AA, starting in Column 5. Each week a new column is added to the right.

Column A has names:

A2: Smith

A3: Jones

A4: Johnson

How would I compare the Names from the 'Associates' tab (beginning in A2) to the tab '2016 - Manager' within the workbook called '2016 Data' by Names (beginning in A2) and then returning the value of  the 2 more recent weeks (Week 25 in Column z and Week 26 in column AA) to Columns K and L.

Why are you putting the weekly data horizontally?  That makes it more complicated than it needs to be.  Vertically would be a lot easier to manage...

However, there's a way to do what you want with the horizontal setup.  This will return the last week hours for the horizontal data:

=INDEX('2016 - Manager'!$1:$1048576,MATCH(A2,'2016 - Manager'!A:A,0),LOOKUP(2,1/('2016 - Manager'!1:1<>""),COLUMN(1:1)))

This will return the hours from 2 weeks ago:

=INDEX('2016 - Manager'!$1:$1048576,MATCH(A2,'2016 - Manager'!A:A,0),LOOKUP(2,1/('2016 - Manager'!1:1<>""),COLUMN(1:1))-1)

 

 

 

 

Share this post


Link to post
Share on other sites

https://drive.google.com/open?id=0B9m547F34ZlBUWJQYmZhbkdjUVU

I have the following formula in my spreadsheet in Column CE

{=SUM(IFERROR((1/COUNTIFS($B$2:$B$3440,$B$2:$B$3440,$T$2:$T$3440,$T$2:$T$3440,$B$2:$B$3440,$B2)),0))}

Goal of Column CE: When the Order Number is the same, column CE should output the total number of jobs (Column T) for that order. 

Is there another way to NOT use the brackets in this formula? They seem to be significantly slowing my larger spreadsheet down.

- When Column T (Job Number) has the same order number (Column B), add up the total of unique job's (Column T) in Column CE. 
For Order# 621811, there are 5 rows with a unique Job Name in Column T. Count this in CE as a '5' for all rows for this Order.
For Order# 621813, 621815, 621816, 621817 and 621818, there is 1 row with a unique Job Name in Column T. Count this in CE as a '1' for all rows in this Order.
For Order# 621819, there are 5 rows, but only 4 with a unique Job Name in Column T. Count this in CE as a '4' for all rows for this Order.

- If an order has more than 1 blank job(s) in Column T and unique job names (not blank), count the number of unique values (not blank) and default a value of 1 for all blank jobs (whether it's 1 or many blank jobs). Order 999999 is an example in the attached.

- If an order has all blank job(s) in Column T, (Order 888888 in the attached example) default column CE to a value of 1.

Please help - my current formula appears to be working, but, as mentioned above, it's just not well with more data.

 

 

 

 

Edited by cubd8

Share this post


Link to post
Share on other sites
On 7/7/2016 at 7:42 PM, cubd8 said:

https://drive.google.com/open?id=0B9m547F34ZlBUWJQYmZhbkdjUVU

I have the following formula in my spreadsheet in Column CE

{=SUM(IFERROR((1/COUNTIFS($B$2:$B$3440,$B$2:$B$3440,$T$2:$T$3440,$T$2:$T$3440,$B$2:$B$3440,$B2)),0))}

Goal of Column CE: When the Order Number is the same, column CE should output the total number of jobs (Column T) for that order. 

Is there another way to NOT use the brackets in this formula? They seem to be significantly slowing my larger spreadsheet down.

- When Column T (Job Number) has the same order number (Column B), add up the total of unique job's (Column T) in Column CE. 
For Order# 621811, there are 5 rows with a unique Job Name in Column T. Count this in CE as a '5' for all rows for this Order.
For Order# 621813, 621815, 621816, 621817 and 621818, there is 1 row with a unique Job Name in Column T. Count this in CE as a '1' for all rows in this Order.
For Order# 621819, there are 5 rows, but only 4 with a unique Job Name in Column T. Count this in CE as a '4' for all rows for this Order.

- If an order has more than 1 blank job(s) in Column T and unique job names (not blank), count the number of unique values (not blank) and default a value of 1 for all blank jobs (whether it's 1 or many blank jobs). Order 999999 is an example in the attached.

- If an order has all blank job(s) in Column T, (Order 888888 in the attached example) default column CE to a value of 1.

Please help - my current formula appears to be working, but, as mentioned above, it's just not well with more data.

 

 

 

 

Any idea on this? thank you!

Share this post


Link to post
Share on other sites

Bump for the day time crowd:

Here's a recap of my issue -

This formula is in Column C of my .xls - When the Order Number (Column A) is the same, column C should output the total number of jobs (Column B) for that order.
=SUM(IFERROR((1/COUNTIFS($A$2:$A$25,$A$2:$A$25,$B$2:$B$25,$B$2:$B$25,$A$2:$A$25,$A2)),0))

Currently, my formula is within an array formula (with the brackets around this formula using Cntl+Shift and Enter - my sample spreadsheet is working fine, but when I use this formula in a larger spreadsheet (with approximately 4000 rows and 60 columns), this formula is grinding to a halt and and my spreadsheet isn't working any longer.

Question - How can this formula be modified to work within my larger spreadsheet?
 

Can this be done without an Array?

Share this post


Link to post
Share on other sites
2 hours ago, cubd8 said:

Bump for the day time crowd:

Here's a recap of my issue -

This formula is in Column C of my .xls - When the Order Number (Column A) is the same, column C should output the total number of jobs (Column B) for that order.
=SUM(IFERROR((1/COUNTIFS($A$2:$A$25,$A$2:$A$25,$B$2:$B$25,$B$2:$B$25,$A$2:$A$25,$A2)),0))

Currently, my formula is within an array formula (with the brackets around this formula using Cntl+Shift and Enter - my sample spreadsheet is working fine, but when I use this formula in a larger spreadsheet (with approximately 4000 rows and 60 columns), this formula is grinding to a halt and and my spreadsheet isn't working any longer.

Question - How can this formula be modified to work within my larger spreadsheet?
 

Can this be done without an Array?

If the problem is performance when scaling the amount of data you're looking at, have you considered moving this into a database like Acess instead of using Excel?

  • Like 1

Share this post


Link to post
Share on other sites

Need some help.  

  • I'm trying to compare data from two sheets.
  • Sheet A has about ten columns with column one being a unique number and the rest of the columns being data
    • Example
      •        A          |  B  |  C  |  D  |  E  |  F  |  G  |  H  |  I  |  J  |
      • 123456789 |  q  |  e   |  r   |  4  |  6  |  i    |  F   |  c |  p  |
  • Sheet B has two columns, column one could contain the unique number in sheet A, column two being other data.
    • Rows that have an SSN will not have any info in the data column and rows that have a data will not have any info in the SSN column
      • Example:
      •         A          | B |
      • 123456789  |    |
      •                     | 3 |

What I need is:

If the value in sheet2 column A exists in sheet1 column A, return sheet 1 column B.  If NOT, return sheet2, column B.

  • Example return:
    • 123456789  |  q  |  e   |  r   |  4  |  6  |  i    |  F   |  c |  p  |
    •                     |  3  |

 

Essentially, I need to build an if/then into a vlookup and i'm too dumb to do it.

Edited by Kanil

Share this post


Link to post
Share on other sites

Iferror(vlookup,value you want to return if vlookup fails)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.