I have a spreadsheet with 3 columns of data as follows

Col 1 Col 2 Col 3
Store Stock Item Sales
Item 1 22
Item 2 322
Item 3 16
Store 1 360
Item 1 11
Item 2 122
Item 3 46
Item 4 31
Store 2 210
etc

How can I easily get the store name to autofill upwards so it appears
alongside each stock item, without copy/paste or dragging each one
individually, so that I can then run a pivot table on the data

Note that each store does not necessarily carry the same number of
stock items

Thanks in advance

Paul


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=547424

Try this:

Select the column with Store Names

From the Excel main menu:
<edit<goto...Click the [special cells] button
Check: Blanks...Click the [OK] button
(Now all of the blank cells are selected)

Press the [=] key
Press the down arrow ONE time
Hold down the [ctrl] key and press [enter]

Now the formerly blank cells should display the correct Store Name
Select that column of cells
<edit<copy
<edit<paste special<values

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Paul Sheppard" wrote:


I have a spreadsheet with 3 columns of data as follows

Col 1 Col 2 Col 3
Store Stock Item Sales
Item 1 22
Item 2 322
Item 3 16
Store 1 360
Item 1 11
Item 2 122
Item 3 46
Item 4 31
Store 2 210
etc

How can I easily get the store name to autofill upwards so it appears
alongside each stock item, without copy/paste or dragging each one
individually, so that I can then run a pivot table on the data

Note that each store does not necessarily carry the same number of
stock items

Thanks in advance

Paul


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=547424

You can use a formula, but you will have to start from the bottom of the sheet.

Steps:

Insert a column before Col 1
Go to the end of the list
Assume that row 100 is the last row in your sheet enter in Cell A100 the
formula :

=If(C100="",B100,B101)
Copy this formula upwards and you will have the Store ID before each item.

HTH

Hans

"Paul Sheppard" wrote:


I have a spreadsheet with 3 columns of data as follows

Col 1 Col 2 Col 3
Store Stock Item Sales
Item 1 22
Item 2 322
Item 3 16
Store 1 360
Item 1 11
Item 2 122
Item 3 46
Item 4 31
Store 2 210
etc

How can I easily get the store name to autofill upwards so it appears
alongside each stock item, without copy/paste or dragging each one
individually, so that I can then run a pivot table on the data

Note that each store does not necessarily carry the same number of
stock items

Thanks in advance

Paul


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=547424

Thanks Ron, that worked perfectly it will save me a bundle of time every
month when I need to manipulate the data for my reports

Would that also work if the store name was at the top and i wanted to
fill down?

Ron Coderre Wrote:
Try this:

Select the column with Store Names

From the Excel main menu:
<edit<goto...Click the [special cells] button
Check: Blanks...Click the [OK] button
(Now all of the blank cells are selected)

Press the [=] key
Press the down arrow ONE time
Hold down the [ctrl] key and press [enter]

Now the formerly blank cells should display the correct Store Name
Select that column of cells
<edit<copy
<edit<paste special<values

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Paul Sheppard" wrote:


I have a spreadsheet with 3 columns of data as follows

Col 1 Col 2 Col 3
Store Stock Item Sales
Item 1 22
Item 2 322
Item 3 16
Store 1 360
Item 1 11
Item 2 122
Item 3 46
Item 4 31
Store 2 210
etc

How can I easily get the store name to autofill upwards so it
appears
alongside each stock item, without copy/paste or dragging each one
individually, so that I can then run a pivot table on the data

Note that each store does not necessarily carry the same number of
stock items

Thanks in advance

Paul


--
Paul Sheppard



------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread:
http://www.excelforum.com/showthread...hreadid=547424




--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=547424

Thanks for the feedback....I'm glad that worked for you.

Yes...that method also works for filling from the top.
In that case, though, you'd use the UP arrow in the formula, instead of the
DOWN arrow.

***********
Regards,
Ron

XL2002, WinXP


"Paul Sheppard" wrote:


Thanks Ron, that worked perfectly it will save me a bundle of time every
month when I need to manipulate the data for my reports

Would that also work if the store name was at the top and i wanted to
fill down?

Ron Coderre Wrote:
Try this:

Select the column with Store Names

From the Excel main menu:
<edit<goto...Click the [special cells] button
Check: Blanks...Click the [OK] button
(Now all of the blank cells are selected)

Press the [=] key
Press the down arrow ONE time
Hold down the [ctrl] key and press [enter]

Now the formerly blank cells should display the correct Store Name
Select that column of cells
<edit<copy
<edit<paste special<values

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Paul Sheppard" wrote:


I have a spreadsheet with 3 columns of data as follows

Col 1 Col 2 Col 3
Store Stock Item Sales
Item 1 22
Item 2 322
Item 3 16
Store 1 360
Item 1 11
Item 2 122
Item 3 46
Item 4 31
Store 2 210
etc

How can I easily get the store name to autofill upwards so it
appears
alongside each stock item, without copy/paste or dragging each one
individually, so that I can then run a pivot table on the data

Note that each store does not necessarily carry the same number of
stock items

Thanks in advance

Paul


--
Paul Sheppard



------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread:
http://www.excelforum.com/showthread...hreadid=547424




--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=547424