2 questions

[bob122]

Hi guys, I am fairly new to excel so I don't know very much. I have done A LOT of googling and trying to figure things out - and so far I have succeeded pretty well. However, there are 2 things that I can't seem to find the answer to (and honestly I have a difficult time understanding some solutions). So I really need some help.

Question 1
In excel, there is an auto copy/fill feature that you can use by clicking/dragging down from the corner of a cell. E.g.:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Will turn into
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Now let's imagine I had the word "Bob" in A1....and the word "Sally" in A3.....and the word "Terry" in A5. I would like to be able to drag down and get Bob to autofill in A1 and A2, and get Sally to autofill in A3 and A4....and so on. So for example:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[/TR]
</tbody>[/TABLE]

The reason I ask is because I am dealing with documents every day with over 1000 rows of names that I need to be autofilled going down. It is too time consuming to do it one name at a time - that is to say - to drag Bob down, then Sally, then Terry, and so on. Is it possible to do this in an easy way? The words change with each document and are rarely in the same order.

Question 2

Okay so I would like to know how I can turn specific words into specific numbers. For example the word "Bob" should turn into a 1. The word "Sally" into a 7. The word "Terry" into an 11. Also, I might have multiple different words that need to turn into the same number, so perhaps "Rachael" should also turn into an 11.
I have over a 100 different words which only need to turn into about 15 different numbers.
I would imagine that the answer lies in scripting/macros, but unfortunately it is far too much for my brain to handle.

Is anyone able to help? It would be GREATLY appreciated.

 

[Fluff]

Hi & welcome to the board
Try this for the 1st question

Sub AddNames()
With Range("A:A")
   .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   .Value = .Value
End With
End Sub

This needs to go in a regular module
To install macros have a look here http://www.contextures.com/xlvba01.html

 

[pagrender]

Hi there,

For Question #1 , you can do the following:
1. Highlight all the data
2. Hit F5, which brings up the GoTo Special dialog box
3. Choose Blanks
4. Type the equal sign and hit the up arrow (do not hit enter yet)
5. Hit Control and Enter. This should have filled in all the blanks with the data in the cell above it.

Does that help?

Pete

 

[pagrender]

I need to edit my last post:

Hi there,

For Question #1 , you can do the following:
1. Highlight all the data
2. Hit F5 and choose Special, which brings up the GoTo Special dialog box
3. Choose Blanks
4. Type the equal sign and hit the up arrow (do not hit enter yet)
5. Hit Control and Enter. This should have filled in all the blanks with the data in the cell above it.

Does that help?

Pete[/QUOTE]

 

[jproffer]

Question 2, you could make a table like this:

Excel 2013/2016
	A	B
1	Bob	1
2	Sally	7
3	Terry	11
4	Rachel	11
5	Joe	15
6	Mike	13
7	Tom	10
8	Aaron	8
Sheet1

and then do a Vlookup to find the person's "number".

 

[bob122]

Hi there,

For Question #1 , you can do the following:
1. Highlight all the data
2. Hit F5, which brings up the GoTo Special dialog box
3. Choose Blanks
4. Type the equal sign and hit the up arrow (do not hit enter yet)
5. Hit Control and Enter. This should have filled in all the blanks with the data in the cell above it.

Does that help?

Pete

Thank you both for your answers! I chose this method because I've been finding the GoTo Special function very useful when it comes to figuring out other solutions that I've needed! After a little tweaking on my worksheet I got this method to word.
Has me all excited because now I just need help with my second question

 

[bob122]

Question 2, you could make a table like this:

Excel 2013/2016

	A	B
Bob
Sally
Terry
Rachel
Joe
Mike
Tom
Aaron

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]11[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]11[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8[/TD]

</tbody>

Sheet1​

and then do a Vlookup to find the person's "number".

I ended up googling "Vlookup" and watching a video about it. Unfortunately doing it this way won't allow me to achieve my end goal (as far as I can tell). Maybe if I give a bit more info it might help. I work at a grocery store. The store has 13 aisles. Each aisle has multiple sections, for example aisle 2 has "tea", "coffee", "biscuits"....and so on. Every day I get a list that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Section[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Tea[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Biscuits[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Eggs[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

(On a side note, some nights the words "tea" or "coffee" or whatever may not appear on the list at all - hence it is different every single time.) My goal is to change those words into the specific aisle they pertain to. For example

[TABLE="width: 500"]
<tbody>[TR]
[TD]Aisle (ie section)[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Eggs[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

And of course then I would do the same with "chicken" and "eggs".
Then ultimately I want to add up all of aisle 2 to get the total sum. And so on for every other aisle. With the goal being to get a total amount for that aisle. Unfortunately the list I am given has over 100 sections all in random orders so it will be a pain to do - but I am willing to try anything.

If Vlookup can help please let me know, it's just at first glance it didn't seem to be able to help me achieve my end goal. Thank you so much for the response though

 

[bob122]

Sorry if double posting isn't allowed. I have been playing around with the SUMIF function. I figured I could get the same result without have to change the word to the number I want.
This is what I've done:

=SUMIF(G20:G22,"tea",H20:H22) +SUMIF(G20:G22,"coffee",H20:H22) +SUMIF(G20:G22,"biscuits",H20:H22)

The problem that I can see with this method is that the range, and sum_range will change for each document/worksheet. Is there a way to streamline this process or perhaps do it another way?