What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

***Official*** Excel Help Corner (2 Viewers)

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.

 
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.

Code:
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
 
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-AsNewModifiedColumn C:StandardModerateComplexIF 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) isThanks!
 
@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.

 
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.

 
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:

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

 
=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]])"))

 
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?

 
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))

 
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.

 
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.

 
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.

 
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 

 
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. 

 
Thanks!  Will try this. 


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. 

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

Code:
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

 
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))))

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

Code:
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!

 
  • Smile
Reactions: Ned
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.

 
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.

 
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?

 
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.

 
Last edited by a moderator:
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.

 
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)

 
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.

 
Last edited by a moderator:
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!

 
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?

 
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?

 
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.

 
Last edited by a moderator:
Iferror(vlookup,value you want to return if vlookup fails)
Can you explain this a little further?  

I get this far:

=IFERROR(VLOOKUP(B:B,'Case data'!A:K,1,FALSE)

But then get stuck on the return value portion.

Also to note, the rows between sheet A and sheet B are not in an exact order so the unique number from sheet a, row 1 may match the unique number from sheet b, row 15.

 
You have to add the default to that.  =IFERROR(VLOOKUP(B:B,'Case data'!A:K,1,FALSE) , Default)

Well, whatever value you want, not the "default word"  Let me set up a mock excel sheet and show you, give me a sec.

 
Last edited by a moderator:
You have to add the default to that.  =IFERROR(VLOOKUP(B:B,'sheet1'!A:K,1,FALSE) , Default)

Well, whatever value you want, not the "default word"  Let me set up a mock excel sheet and show you, give me a sec.
If it matches I'd want the returned value from the vlookup (in the example, "Sheet1, column A").  If not, "Sheet2, column B".

 
=IFERROR(VLOOKUP(A1,Sheet1!A:H,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE))
 

Where is the value that you're trying to match, as in, where does this formula sit? Your vlookup will never work, because you're trying to find every value in B, you gotta give it one value to find.

 
Did not read the whole thread but I will if you think Excel is the right program for me.

I know very little about the program but I want to develop a way to track visitors that come to our church.  A program to store their contact information so we can send them information down the line. We would want to be able to add to it each time a new individual/family visits. Is Excel my program?

There are church programs for that kind of stuff but we are a small church with a small budget, so we would prefer to not purchase an additional program if not necessary. 

Thanks in advance! 

 
Did not read the whole thread but I will if you think Excel is the right program for me.

I know very little about the program but I want to develop a way to track visitors that come to our church.  A program to store their contact information so we can send them information down the line. We would want to be able to add to it each time a new individual/family visits. Is Excel my program?

There are church programs for that kind of stuff but we are a small church with a small budget, so we would prefer to not purchase an additional program if not necessary. 

Thanks in advance! 
It can easily be used to do this. Just set up a basic Excel sheet with the fields you want, likely: Name, Street Address, City, State, Zip Code - plus anything else you may want such as date of visit or any comments you may want regarding them.

From that setup you can easily create mailing labels in Word if you are so inclined, or have access to it for handwriting mailing addresses. Also, by having it in Excel, you can easily filter the information if you want to send targeted information - ie just to visitors from a particular city or zip code, etc.

 
Beef Ravioli - what you need to do is called CRM (Customer Relationship Management).  There are free web CRM apps out there that will probably work for you.  I don't know a lot about this, so I can't recommend one.

 
acarey50 said:
It can easily be used to do this. Just set up a basic Excel sheet with the fields you want, likely: Name, Street Address, City, State, Zip Code - plus anything else you may want such as date of visit or any comments you may want regarding them.

From that setup you can easily create mailing labels in Word if you are so inclined, or have access to it for handwriting mailing addresses. Also, by having it in Excel, you can easily filter the information if you want to send targeted information - ie just to visitors from a particular city or zip code, etc.
Thanks! 

 
Thorpe said:
Beef Ravioli - what you need to do is called CRM (Customer Relationship Management).  There are free web CRM apps out there that will probably work for you.  I don't know a lot about this, so I can't recommend one.
Thanks! I will check it out. 

 
@Kanil

Kanil said:
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.
Where are you actually placing this formula?  I'm assuming you're putting it in Sheet2 Column C??  Otherwise, I agree with @Spin since you have to have a key to lookup.  The below formula assumes you're putting this in column C of Sheet2.  If not, we need more info!

=IFERROR(VLOOKUP(Sheet2!A2,Sheet1!A:K,2,FALSE),B2)

 
Last edited by a moderator:
Can you explain this a little further?  

I get this far:

=IFERROR(VLOOKUP(B:B,'Case data'!A:K,1,FALSE)

But then get stuck on the return value portion.

Also to note, the rows between sheet A and sheet B are not in an exact order so the unique number from sheet a, row 1 may match the unique number from sheet b, row 15.
Are they sorted on sheet A? I believe with vlookup you'll have a problem if they aren't.

 
Did not read the whole thread but I will if you think Excel is the right program for me.

I know very little about the program but I want to develop a way to track visitors that come to our church.  A program to store their contact information so we can send them information down the line. We would want to be able to add to it each time a new individual/family visits. Is Excel my program?

There are church programs for that kind of stuff but we are a small church with a small budget, so we would prefer to not purchase an additional program if not necessary. 

Thanks in advance! 
Alright, I have started working on my "prospect list". I have headings as follows:

Last Name, First Name, Age/Grade, Address, Zip Code, Phone, Email, Date of Church Visit, Date of Followup Visit, Persons Making Visit, Comments, Interest Level of Prospect

Right now I have entered 22 of 100ish names and addresses, etc. I want to be able to print off individual prospects without printing out the whole sheet. I also want it in a format that does not look like a spreadsheet. If possible, I would like this to be able to be printed out on prospect cards (perhaps a 4x6).  Is this possible?

After googling, it says I should have a "Report Manager" under "View" with ways to print reports. I don't have that or I cannot find it. 

Thanks!

 
Alright, I have started working on my "prospect list". I have headings as follows:

Last Name, First Name, Age/Grade, Address, Zip Code, Phone, Email, Date of Church Visit, Date of Followup Visit, Persons Making Visit, Comments, Interest Level of Prospect

Right now I have entered 22 of 100ish names and addresses, etc. I want to be able to print off individual prospects without printing out the whole sheet. I also want it in a format that does not look like a spreadsheet. If possible, I would like this to be able to be printed out on prospect cards (perhaps a 4x6).  Is this possible?

After googling, it says I should have a "Report Manager" under "View" with ways to print reports. I don't have that or I cannot find it. 

Thanks!
I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.microsoft.com/en-us/kb/294683

 

Users who are viewing this thread

Top