Excel query

  freddy-firecracker 20:46 23 Jun 2006


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.


  Jackcoms 20:49 23 Jun 2006

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

  freddy-firecracker 21:16 23 Jun 2006

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 2006

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 2006

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 10:41 24 Jun 2006

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

  Noldi 20:41 24 Jun 2006

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.

  VoG II 21:52 24 Jun 2006

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires