dslreports logo
 
    All Forums Hot Topics Gallery
spc
Search similar:


uniqs
197

Hall
MVM
join:2000-04-28
Germantown, OH

Hall

MVM

[Excel] Add multiple rows, transpose data

I have a table with city names, zip codes, county names, and sales tax % for Ohio that I need modified. For all but (10) cities, I've split things up so that it looks like this:

Westerville 43081 Delaware 7.50
Westerville 43082 Delaware 7.50
Westerville 43081 Franklin 7.50
Westerville 43082 Franklin 7.50
West Unity 43570 Williams 7.25
West Union 45693 Adams 7.25
West Salem 44287 Wayne 6.50

Note how Westerville is duplicated (4) times because it has (4) different zip codes while the others are one line each.

For the (10) cities I mentioned at first, Columbus, for example, it currently looks like this:

Columbus 43201 thru 43299 Franklin 7.50


How do I easily insert (97) blank lines ? I know you can select 'x' cells and then use the 'Insert' function to insert 'x' rows and for 2, 3, 5 rows, that's feasible. For (97), not so much. I can transpose the data if I can get past this.
towerdave
join:2002-01-16
O Fallon, IL

towerdave

Member

When I click and drag to highlight row numbers, it tells me how many rows are highlighted. It only took a few seconds to highlight 97 rows. Right underneath the cursor, it should show a number and the R for rows.

Or am I missing something?

TD

Hall
MVM
join:2000-04-28
Germantown, OH

Hall

MVM

I see what you're referring to and I used that to help get the right number of rows. It took a few tries to determine what to select/highlight in order to get those rows inserted. I still haven't figured out how to get blank rows added but that's not a deal-breaker.

For now, I add a single, blank row below the data I want to copy. Then, select that (entire) row and highlight down until I get (97). Go back to the blank row and Insert Copied Cells. If you want, you can "Clear Contents" or simply do the highlight-click-drag to duplicate the data.

2kmaro
Think

join:2000-07-11
Oklahoma City, OK

2kmaro

Not sure if I quite understand properly or not, but you can select an entire row and then Insert a row there. Then you can hit [Ctrl]+[Y] 96 more times - or just a bunch of times (you can always delete extra rows later).

Ctrl+y is "repeat last command" shortcut command.

I would have thought that copying 97 rows and pasting with an insert would have done the trick for you also.

Hall
MVM
join:2000-04-28
Germantown, OH

Hall

MVM

said by 2kmaro:

Ctrl+y is "repeat last command" shortcut command.

That keystroke doesn't appear to work on the Mac version of Excel.

...but %u2318+y does.