[Solved] Auto-fill for Adjacent Cells with Same Text

[jglag]

Hi there,
I tried searching for this in the forums but couldn't find what I was looking for. Sorry if it seems like an easy question!

I have a spreadsheet with a list of names in one column, and numbers in the column to the right of them. The names contain many, many duplicates, and every time the name is the same, the corresponding number is the same. This number changes around daily, so I have to go through and manually change the number for each name every day, which takes a lot of time. There would be 2 ways to speed this up, though I can't figure out how to do either of them.

1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."

2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above. So, like a "Master" column that when edited fills in the rest.

#2 seems more probable, and I'd love to know how to do this.

Thank you so much!

Using OS X 10.7.5

[MrProgrammer]

1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."
2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above.

Both methods are demonstrated on the attached spreadsheet. #1 is performed with Data → Filter → AutoFilter. #2 is performed with VLOOKUP. Method 2 will be much less work once you create the auxillary table.

You can do the initial creation of that table using Data → Pivot Table (formerly Data Pilot). Read about that in the Help, in this forum, or in the Wiki. Put the names in the Row Fields area. Use the AVERAGE function to summarize the number column in the Data Fields area of the pivot table.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[jglag]

1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."
2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above.

Both methods are demonstrated on the attached spreadsheet. #1 is performed with Data → Filter → AutoFilter. #2 is performed with VLOOKUP. Method 2 will be much less work once you create the auxillary table.

You can do the initial creation of that table using Data → Pivot Table (formerly Data Pilot). Read about that in the Help, in this forum, or in the Wiki. Put the names in the Row Fields area. Use the AVERAGE function to summarize the number column in the Data Fields area of the pivot table.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Thank you very much!

I am trying to use the 2nd method, but I am encountering a problem. It works for the first time, but then when I try to copy the formula for the cell below, it says #N/A, even if I change the cell it's pulling from. The array it's pulling from is the same.

Thoughts?

[jglag]

In your spreadsheet when you select and drag down, OR manually change the number of the cell it's pulling form (ex: A1 to A2), it works, but when I do it in my spreadsheet, it says #N/A when I make the same change, regardless.

Thanks.

[jglag]

UPDATE:

This is extremely bizarre. When I drag the formula down, it works correctly for SOME cells, and for others it says #N/A. I thought it had something to do with spelling, but it doesn't seem to. I figured I'd go through and "ignore all" spelling mistakes in spellcheck. It still doesn't work. I'm 100% sure that everything is spelled correctly and that the names are all spelled the same way, from both tables.

What a strange and confusing situation. Any ideas...?

[MrProgrammer]

When I drag the formula down, it works correctly for SOME cells, and for others it says #N/A.

If your VLOOKUP function doesn't work the way you expect, consider that there are many possible difficulties (especially Q5, Q6, Q11, Q13, Q14, Q16, Q17, and Q19). If you're tried the solutions here and are still stumped, when creating a new topic in the Calc section be sure to attach your document (not a picture of it). Otherwise it is hard for the volunteers to help you because in most cases a picture doesn't have enough information to diagnose the problem.

You are using VLOOKUP but didn't read the tutorial that I linked for you in my earlier post, right? Study the tutorial before attempting to use the function. Your situation is almost certainly covered there. You might also want to read [Tutorial] Ten concepts that every Calc user should know. Spending ten minutes reading that might easily save you ten hours of frustration.

[jglag]

Hi there,
I read the online documentation and followed your spreadsheet at carefully as possible, but it still confuses me. I'm not quite sure what I'm doing wrong.

Here I have attached my spreadsheet. Let me explain where this is all coming from:

Sheet: Formula Test (The last sheet)
Names go from B3 to B18, and corresponding value is in the column to the right, under "Raw Points." Please disregard the "final points" column.
The name table I am drawing my VLOOKUP from starts at R2 on that same sheet.

Thank you very much for any help.

[gerard24]

I see only one #N/A.

Have a look

and you may see why.

You should also read the part of the tutorial about absolute and relative reference.

[jglag]

When I copy the formula, even in batch, how do I make it search form the same table, and not move everything it's searching for down? I'd like it to be able to be dragged correctly.

I will read through everything and gather as much knowledge on the intricacies of this software as soon as I can. I'm sorry for not being 100% well-read in all the details. It's just I run a fairly high-profile fantasy tennis league and we are going under a switch in score calculation, and I need to get this finished very quickly. I appreciate all the help you guys are giving me given how quickly I need this up and working.

Thank you so much.

[gerard24]

When I copy the formula, even in batch, how do I make it search form the same table, and not move everything it's searching for down? I'd like it to be able to be dragged correctly.

=VLOOKUP(B3;R2:S81;2;0) reference is relative and change when dragging.
=VLOOKUP(B3;$R$2:$S$81;2;0) reference of the table is absolute and don't change.

[jglag]

When I copy the formula, even in batch, how do I make it search form the same table, and not move everything it's searching for down? I'd like it to be able to be dragged correctly.

=VLOOKUP(B3;R2:S81;2;0) reference is relative and change when dragging.
=VLOOKUP(B3;$R$2:$S$81;2;0) reference of the table is absolute and don't change.

You're wonderful, thank you so much. Have a great day.