Compiling complex text data in a table

**C_P** · 07-19-2011, 08:25 AM

Okay, so, I’m looking to put a formula into excel which does the following:

I want the data from column ‘H’ and column ‘I’ (shown on image 1) to collect and show up in a table on a separate worksheet (image 2) automatically, without me having to input the data myself. However, I have a feeling this will be complicated as each cell in the table will need different circumstances in order to count the instance.

(apologies, wasn't sure how to inbed the images in this post)

Image 1: http://img.photobucket.com/albums/v38/Kimaria/data1.jpg

Image 2: http://img.photobucket.com/albums/v38/Kimaria/data2.jpg

Example:
If counselling shows up alongside the postcode AB10, count as 1 within the cell: B2. Etc…etc…

The purpose of this is to show what services are being commissioned under a certain postcode.

Is this possible to do via the DCOUNTA function or by using a Pivot Table? (Neither of which I have any experience with, unfortunately). If anyone would be able to point me in the right direction or what formula I need to do this, that would be great!

Thanks in advance for your help and apologies if my explanation was poor. If you need more information, I’d be happy to provide it.

**arthurbr** · 07-19-2011, 08:45 AM

Hi and welcome to the board.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**C_P** · 07-19-2011, 09:52 AM

Thanks for the advice, arthur. I've managed to attach the dummy data with a small example of what I mean.

Hopefully this will be more helpful.

**arthurbr** · 07-19-2011, 10:04 AM

A pivot table might be a possibility

See attached

I have used a dynamic range called dbase - You can get more info on this subject at http://www.contextures.com/xlPivot01.html

**martindwilson** · 07-19-2011, 10:19 AM

or use sumproduct in b2 put
=SUMPRODUCT(--(TEST2!$H$4:$H$1000=$A2),--(TEST2!$I$4:$I$1000=B$1))
drag across and down

**C_P** · 07-19-2011, 10:42 AM

Thank you both so much! That's so useful! I'll have a play with both options and see which one I find easier to work with.
Thanks again for your time. It's really aprpeciated!