Microsoft Office/Excel Question

[robbiestoupe]

http://www.sports-reference.com/cbb/pla ... der_by=pts

Filter/sort by player

[Kraftster]

Woah. That's pretty sweet. I might be able to work with that instead of pulling game logs for each player.

[willeyeam]

whoa so i'm not the only nerd that makes a big excel spreadsheet to rank players for a March Madness draft

[willeyeam]

Struggling to find an answer, and I realized this is the biggest group of nerds I know.

I have 2 worksheets detailing health insurance- we'll call them Employees and Dependents. Each employee could have multiple dependents on their insurance plan. The employee and related dependents have an ID # that matches. I need all of the dependents on the dependent worksheet to be inserted on the same row as their employee.

So basically, if the ID# on the employee sheet matches the ID# on the dependent sheet, I need to add the dependent info on the same row as the employee. Is this possible? Thank you!

Sounds like a job for VLookup! Although it will have to be tweaked if an ID can have more than 1 dependent, which I'm sure is the case.

Someone might have a better idea but off the top of my head, on the dependent page you could have mini tables that have one unique id per mini table. Sort dependent page by id number and then pull out duplicates across new tables until you have unique ids in every 2 column table.

Column A and B - A is ID and B is name

Column C and D - C is ID and D is name

so on and so forth.

Then a vlookup would work in each mini table.

That would work, but could be tedious if the list is very long. You could also write an easy "IF" statement on the dependent worksheet that checks whether the ID before it is the same. If no, the value is 1. If yes, the value is previous cell +1. In a column next to that, you concatenate the two values so you have an ID with a suffix that is the number of the dependent.

In your employee worksheet, you have a column for the employee, then columns for dependents (1-N). Write the vlookup formula but look for user ID&1 for dependent 1 column, user ID&2 for dependent 2 column etc. You'll have to truncate the last digit of the dependent ID number in another column so that the ID numbers match.

You could hide all the other columns that don't show the real results you need to see so that the spreadsheet isn't a mess.

So I never had to go through this. The client is supposedly sending a new sheet with everything formatted correctly.

[Kraftster]

Thanks a ton @robbiestoupe. That approach saved me a ton of time and is only slightly less comprehensive than what I was trying to do. The real pain was only 100 at a time. I had to import from 39 different pages to get down to all 3900 games with 22 points or more scored.

I am posed to dominate the competition this year, I think.

[robbiestoupe]

Struggling to find an answer, and I realized this is the biggest group of nerds I know.

I have 2 worksheets detailing health insurance- we'll call them Employees and Dependents. Each employee could have multiple dependents on their insurance plan. The employee and related dependents have an ID # that matches. I need all of the dependents on the dependent worksheet to be inserted on the same row as their employee.

So basically, if the ID# on the employee sheet matches the ID# on the dependent sheet, I need to add the dependent info on the same row as the employee. Is this possible? Thank you!

Sounds like a job for VLookup! Although it will have to be tweaked if an ID can have more than 1 dependent, which I'm sure is the case.

Someone might have a better idea but off the top of my head, on the dependent page you could have mini tables that have one unique id per mini table. Sort dependent page by id number and then pull out duplicates across new tables until you have unique ids in every 2 column table.

Column A and B - A is ID and B is name

Column C and D - C is ID and D is name

so on and so forth.

Then a vlookup would work in each mini table.

That would work, but could be tedious if the list is very long. You could also write an easy "IF" statement on the dependent worksheet that checks whether the ID before it is the same. If no, the value is 1. If yes, the value is previous cell +1. In a column next to that, you concatenate the two values so you have an ID with a suffix that is the number of the dependent.

In your employee worksheet, you have a column for the employee, then columns for dependents (1-N). Write the vlookup formula but look for user ID&1 for dependent 1 column, user ID&2 for dependent 2 column etc. You'll have to truncate the last digit of the dependent ID number in another column so that the ID numbers match.

You could hide all the other columns that don't show the real results you need to see so that the spreadsheet isn't a mess.

So I never had to go through this. The client is supposedly sending a new sheet with everything formatted correctly.

Good thing I do my work pro bono

[robbiestoupe]

Thanks a ton @robbiestoupe. That approach saved me a ton of time and is only slightly less comprehensive than what I was trying to do. The real pain was only 100 at a time. I had to import from 39 different pages to get down to all 3900 games with 22 points or more scored.

I am posed to dominate the competition this year, I think.

Ha, still seems like a lot of work.

I used to do stuff like this for fantasy drafts and even one time built a March Madness bracket in Excel. Yeah, I'm an Excel nerd.

[tifosi77]

We're revising our employee handbook. For some reason, the doc we received back from the translation company who prepared the Spanish version is all sorts of wacked out. Most of the bookmarks in the table of contents are broken. I looked up on the MS Office website, "Error! Bookmark not defined" and it said "When you accept revisions, Word removes the deleted table of contents and deletes the 'error.' Similarly, captions and cross-references appear correctly when you accept revisions." There aren't any revisions in the document to accept.



#gearsground

[Kraftster]

I'm so close to figuring this out, but now I'm in this endless loop that I can't find my way out of.

I'm importing the per game stats for tournament teams. I have a list of all the teams. What I'm trying to do is automate the importhtml function for each of the teams so that I don't have to type 64 different importurl functions.

The problem is, Google Sheets/Excel won't pick up on the repeating pattern of the formula because the program adjusts the formula based upon the target cell's relationship to the source cell. If my first importurl function for the first team on the list is in Cell B2, the second one cannot be in B3 (where the program would pick up the pattern) because the table that is imported is multiple lines itself.

So, I used the INDIRECT function in conjunction with ROW to use the actual cell that the formula is in to find the appropriate team to reference:

=ImportHTML(Indirect("TeamFormula!E"&(Row()-18)), "table", 6)

The problem is, I can't just keep using this because each next importhtml is going to be 19 rows from the last, so the number in the formula (18) needs to increase by 19 every team. So, the next one will be:

=ImportHTML(Indirect("TeamFormula!E"&(Row()-37)), "table", 6)

And so on.

So, I was like, well, this is easy, I'll just build a column of the number I need to reference to subtract by each time: 18, 37, 56, 75, etc. But then I run into the same problem! I can't refer to that column consistently without manually adjusting the number because, again, Sheets won't pick up on the pattern since it just continues to adjust it baed upon the target cell's relationsip to the source cell.

Is there any way to pull this off?

[Kicksave]

I'm trying to create a spreadsheet where I want a price to multiply by 10% if it's under a certain number but 10% if it's above a certain number.

Is there a formula for that?

[Troy Loney]

I'm trying to create a spreadsheet where I want a price to multiply by 10% if it's under a certain number but 10% if it's above a certain number.

Is there a formula for that?

If then, Can also write something in VBA to do that.

[willeyeam]

think you can do an if statement, like if A3<10K, A3x10%
and if A3>10K, A3X5% or whatever

[LITT]

Are those the only 2 options? If so the if function will work like a champ.

[Kicksave]

Finally got the IF function to work. Had to tweek it around. Thank you!

[mikey]

New problem

I have an Excel workbook with about 100 worksheets in it. On every worksheet, in column AI there is a list of 20 names (AI2:AI21) - some of these names duplicate in some worksheets, some will appear only once. Is there any way I can grab that list from each sheet and deposit it into one column on a new sheet?

The next step I feel like can be done with VLOOKUP but it's worth saying in case it can't (I haven't gotten this far).

Every name has a set of scores after it (about 20 of them) that I need to combine the respective totals of across all of these sheets. Some of the customization that I'm going to want from this data might just require me to combine all of these worksheets into one gigantic worksheet and work off of it...but I'm not sure...

I know anyone who even attempts to take this on (your funeral) will need more info/visuals, which I can provide...but I don't know if we have an Excel expert here...

[mikey]

I think I figured it out via VB macro...thanks for nothin'...

[mikey]

Nevermind. I'm still a **** up...

Code: Select all

Sub AmalgamateSheets() Dim Ws As Worksheet For Each Ws In Worksheets If Not Ws.Name = "Data" Then Ws.UsedRange.Offset(1).Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1) End If Next Ws End Sub

This made all the values from formulas copied over the exact same...I blew it.

[meow]

New problem

I have an Excel workbook with about 100 worksheets in it. On every worksheet, in column AI there is a list of 20 names (AI2:AI21) - some of these names duplicate in some worksheets, some will appear only once. Is there any way I can grab that list from each sheet and deposit it into one column on a new sheet?

The next step I feel like can be done with VLOOKUP but it's worth saying in case it can't (I haven't gotten this far).

Every name has a set of scores after it (about 20 of them) that I need to combine the respective totals of across all of these sheets. Some of the customization that I'm going to want from this data might just require me to combine all of these worksheets into one gigantic worksheet and work off of it...but I'm not sure...

I know anyone who even attempts to take this on (your funeral) will need more info/visuals, which I can provide...but I don't know if we have an Excel expert here...

[mikey]

Goalies...

[Troy Loney]

Conditional formatting question.

Making a prepopulated checklist, I want it all to default to no, I want a series of items to be blacked out unless one item is changed to yes. How could I have the cells either black out or not based on the one drop down selection?