Excel query

  freddy-firecracker 20:46 23 Jun 06
Locked

Hi

I have a spreadsheet which has numbers down column A and references under these numbers in column B. Some of these references are multiples and some are single;only relating to one number. Imagine it like column A has chocolate manufacturers and column B has types of chocolate. Some will have single entries in column B, some will have several taking up the rows underneath.
Eg A1-Cadbury B1- Dairy Milk, B2 Creme egg, B3 Milk Tray
A4 Rowntrees, B4 Maltesers. Gaps will appear in column A when column B has mutiple entries. Is there a way i can set up a formula to count the mutiple entries relating to one type and the single entries relating to another type. The s/sheet holds upto 10,000 lines of data though.

Any help would be great.

FF

  Jackcoms 20:49 23 Jun 06

Presumably your references to Cadbury are irrelevant now that they've taken their products off the shelves?? ;-))

  freddy-firecracker 21:16 23 Jun 06

very topical, if you know the answer you would be able to help me. Imagine Cadbury was Nestle then...

  VoG II 21:20 23 Jun 06

I think that you will need a macro to do this. Unfortunately I am stranded in Tenerife with no access to Excel. You could try posting this at click here where you can display a picture of your worksheet.

  Noldi 10:39 24 Jun 06

Sorry very busy today I will look again tonight.
But it might be somthing like, Move your data over one coloum to the right (A is empty) and enter in A2.
=IF(B2="",B1,B2), That would give you the gaps filled in A.
Look againn tonight

Noldi

  Noldi 10:41 24 Jun 06

Sorry forgot to mention to drag this formula down the A coloumn. Just tried it and not quite correct.
Noldi

  Noldi 20:41 24 Jun 06

Sorry that formula only works with a single empty cell. As VOG said you need a macro, I will have a look or hope VOGs vacation is near its end.
Noldi

  VoG II 21:52 24 Jun 06

If the sheet does not contain sensitive information you are welcome to email it to me. Please click my yellow envelope to initiate this but I won´t pick it up till Monday at the earliest.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

New Corel ParticleShop plugin now available: 11 new brushes & 6 new brush packs

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…