What should i use to do this?

philscrase

Board Regular
Joined
Feb 1, 2007
Messages
142
I currently have a table which has been extracted from another source. It is full of random information, some complete, some incomplete ....

My horizontal headers are: Code, Description, Area, Cost.

'Code' contains the fields A1, A2, A3, A4, A5, A6, A7, B1, B2, B4, B11, E56, F76, G121.
'Description' is just text which is unimportant.
'Area' contains names of Area (Area1 Area2 Area3 etc)
'Cost' - a random amount in £'s

Areas are listed from 1 to 57 (Area1 through to Area57) - Now the annoying part is that not ALL areas have a value for the 'Code'. For example, where most have something for codes A1 through to G121 there are some Areas whom dont have a value like for code A6, B4, E56 etc. Therefore the line doesnt exist (There is no complete line with the code, desc, Area and cost - the line will just be non existent) I hope i have explained that ok(ish).

What i want to do is to create a table with the codes across the top, Area names down the side (left) and have some kind of function that will match the code with the Area and put the cost in the relevant box. where there is no entry then it would just be blank.

Is there anything that will read a vertical and a horizontal point and find that value if there is one?

I'm not after a great deal of work as i'd imagine it could be quite complicated, but if you could point me in the right direction i can get on with investigating that.

Any help is much appreciated.

I have a complete set of vertical headers which are . Now for some 'Areas' i have info, for others i dont, and it is very messy.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try using a sumproduct, I tried it in a mini-super-simplified table and it worked. Here is what I did, I'm assuming named ranges and that your Codes start in B1 and continue left and that Area starts in A2 and continues down.

=sumproduct(--(area=$A2),--(code=B$1),cost)

Then you should be able to copy and paste down and left. If you use cell references instead of named ranges don't forget to make them static (I always forget).

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top