Need M$ Excel help

Factual Questions
1

When I enter numbers in excel I usually will omit numbers that repeat and fill them in later using copy and paste. IS there an easier way to have this done automatically?

using csv (comma seperated values - a way of denoting spreadsheet columns) - if I were to enter the following

11,23
11,12
11,46
22,46
22,65
22,34

I would usually enter it
11,23
,12
,46
22,46
,65
,34

and copy down 11 and 22 later - I want a way to have excel do that for me (that’s why I pay the big bucks for the MS program).

Also WOW it looks like the board got nuked.

2

That’s how I’d do it, dave. You could also use the autofill instead of C&P, but that’s only marginally less bother.

3

Or you could type a different letter or symbal that your not using in that column to replace each repeated number (such as all 22’s are entered as a “[” ) and use the replace command to replace all ['s to 22’s. Not much of a benefit for smaller numbers I suppose, but would help with larger numbers.

4

I have used all of those methods but I would think there should be a way for excel to do my work.

5

Ther are lots of things you could do depending on the source of you data, the number of cells, how often you update the same spreadsheet.

You could in you example enter

,23
,12
,46
then use find and replace to change all ‘,’ to ‘11,’ then enter
,46
,65
,34
and find and replace all ‘,’ with ‘22,’ and continue like that.

What I would probably do is -

  1. use 3 columns
  2. in cell a1 enter 11, grab the bottom right corner with the crosshair cursor and drag down as many rows as necessary and fill them all with 11.
  3. start entering second number of series in b1 - 23, b2 - 12, b3 -46.
  4. enter 22 in a4 and repeat fill process.
  5. enter from b4 down.
  6. in c1 enter =concatenate(a1,",",b1), grab the fill handle and drag down.

Next time you need to do the same thing it’s all ready to go.

6

I suppose you can write a macro to do it

7

[qutoe]I have used all of those methods but I would think there should be a way for excel to do my work.
[/quote]

Try this, using your example numbers from above:[ul][]Type the “11” in cell a1 (probably not, I understand, but for illustration assume it is)[]in cell a2, type the formula =a1[]copy and paste the formula to fill column a[]Whenever you need to change the value of the number in column a, do so and all values below will be changed tooWhen you’re done, copy all the cells you want to keep (you’ll probably have upwards of 16,000 you don’t :)) and use Paste Special to paste only their values.[/ul]

8

I’ve been having trouble with that qutoe. I’m thinking of trying Qutoe-Riffic to get rid of it.

9

kneedtoknow I think you gave me the answer - a little differnt but it should work.

before the 1st value is typed have cell a2 equal a1. Copy this formula down column a (so a3 = a2, a4=a3). When I enter a value it will overright this formula and change a all valuse below it to the new value. Then when I’m done I can use the paste special command.

thanks I know it could be done.

10

Happy to help, k2dave.

11

In your example, if you have 11 in the first row AND the second row, you can highlight both those rows and just drag the corner of the highlighted section down. It is not really any better than copy and paste for most applictions, but it is handy sometimes. If you only grab one cell and drag it down, it would usually end up being:
11
12
13
14
etc. If you have a pattern of several things that repeats, you can do the pattern that way too.

Fishhead

12

Try this site they might already have what you are looking for in their archives.