Hi I have this excel spreadshee 7

[eXtacy]

Hi

I have this excel spreadsheet which contains clients information. An example of the spreadsheet is below.

id Name Amount NoOfWeeks
line1/ 1 JHON 1 1
line2/ 1 JHON 15 14
line3/ 2 PETER 2 2
line4/ 2 3 3
line5/ 3 CHRIS 8 5
line6/ 3 10 9
line7/ 4 JAMES 4 4
line8/ 4 JAMES 11 10
line9/ 5 HARRY 5 5
lin10/ 5 12 11
lin11/ 6 BRUNO 14 13
lin12/ 6 6 6
lin13/ 7 PATEL 7 6

This is what i am trying to do:

Where cells are NULL in the 'name' column only, i

------
eXtacy

 

[mscallisto]

Brilliant

 

[mscallisto]

Ken

Is there a "similar" trick to do the reverse?

 

[xlbo]

If you have filled out the data using Ken's technique or any other formulae then you can use
Select data area
Edit>Goto>Special>Blanks
Then just hit delete

If not then there is no difference between the cells and you'll have a hard time - although a pivot table will create that kinda layout if you are desperate

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

But they aren't blanks anymore, so won't be selected??

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

D'OH:
Edit>Goto>Special>Formulas

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Hehehehe <Methinks Geoff knows what's coming!!>

[Deadpan]
Geoff - I think we can allow 1 little slipup - taking previous good work into consideration - just don't let it happen again
[/Deadpan]

rotflmao!!!!!

For those on the outside, I'm not being cruel, honest - You just had to be there ;-)

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[mscallisto]

Yes now I understand, there's simply nothing to start with (no blanks).
thank you

 

[xlbo]

ROFLMAO - nice one Ken. Didn't think you'd let that one lie


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[mscallisto]

I've simply got to get with the humor program, 79,200 google hits on ROFLMAO and now I know!

 

[KenWright]

Geoff - Couldn't resist it ;-)

Mscallisto - To be honest though, I'm not quite sure what you mean by reverse. Do you mean delete all entries other than the first of each block?

Regards
Ken...............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[mscallisto]

Yes I did.

I would normally use a few lines of VBA to accomplish this but was curious if excel had a hidden solution like above.

I can see that Edit / Go To / Special / offers no solution.

 

[DennisFromBoulder]

I think this way is better:
1. Select cells in the name column that do or should contain names
2. Press the F5 key, then Special, then Blanks, then Enter
3. Press the equal sign, then up arrow, then Ctrl+Enter
4. Select and Copy all the cells in the column
5. Edit menu, Paste Special, Values, OK

 

[KenWright]

But it's the same?? F5 is just a shortcut for Edit / Go To.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DennisFromBoulder]

Almost the same...
Instead of locating and clicking “on the first cell above the active cell where the = appears”, which could be cumbersome when dealing with a large range, this method simply requires an Up Arrow.

 

[KenWright]

OK, fair comment Dennis. I use both and have no preference over either, just depends where my hands happen to be at the time, but it was a good catch. Have a Star on me, and I'll add it to my FAQ as a clarification.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------