who's really good at excel?

[stilleto]

I have a spreadsheet of organizations. each line has the contact name, the organization, address, phone, email, etc.

some organizations have 3 lines, because i have 3 different contact names.

what i need to do is give each organization it's own promotional code. I was going to put a formula at the top, then fill down, but it means that each contact at the same organization will have a different code, and i want the organization to have only one.

 

[billfred]

I have a spreadsheet of organizations. each line has the contact name, the organization, address, phone, email, etc.

some organizations have 3 lines, because i have 3 different contact names.

what i need to do is give each organization it's own promotional code. I was going to put a formula at the top, then fill down, but it means that each contact at the same organization will have a different code, and i want the organization to have only one.

Instead of putting the address in 3 lines can you put it in one and then format cell, wrap text?

 

[stilleto]

Instead of putting the address in 3 lines can you put it in one and then format cell, wrap text?

no no... the address is all in one line. one field per address, then city, state, and zip.

the problem is that there are different contact names for each organization and the way my spreadsheet is set up- each name has it's own line. So i might have an organization with 5 lines, because i have a director, a chief, an Administrator, a CFO, etc.

but now i need to assign a numerical code (can be anything i want) to each organization, not each name.

 

[billfred]

no no... the address is all in one line. one field per address, then city, state, and zip.

the problem is that there are different contact names for each organization and the way my spreadsheet is set up- each name has it's own line. So i might have an organization with 5 lines, because i have a director, a chief, an Administrator, a CFO, etc.

but now i need to assign a numerical code (can be anything i want) to each organization, not each name.

ok.. collapse it (group it) by organization, assign the numbers, then expand it again.

 

[stilleto]

ok.. collapse it (group it) by organization, assign the numbers, then expand it again.

ah. good... i'll figure out how to do that. if i can, it's perfect.

 

[eddymerckx]

i excel at good...if that helps

 

[SpyWizard]

import into a access data base, then add 1 field..

that's the correct way to do it..

good luck..

 

[stilleto]

i excel at good...if that helps

that's always a help...

 

[hawaiianflash]

You need to create a table of unique records of organization and promo-code.
To do this:
>highight the entire table
>create pivot table
>drop the organization field into the row section

you will now have a list of organizations without duplicates

>copy that list and paste it as values in another worksheet
>paste your promocodes next to the organizations

now you have a table of unique organization and promo codes

>do a vlookup on that table =vlookup(a1<organiztion column>,promocode!a1:b100 <promocode table>,2,false)

 

[stilleto]

You need to create a table of unique records of organization and promo-code.
To do this:
>highight the entire table
>create pivot table
>drop the organization field into the row section

you will now have a list of organizations without duplicates

>copy that list and paste it as values in another worksheet
>paste your promocodes next to the organizations

now you have a table of unique organization and promo codes

>do a vlookup on that table =vlookup(a1<organiztion column>,promocode!a1:b100 <promocode table>,2,false)

that's close, but i need the organization, ALL the email addys, and the promo code for that organization.

i'm going to do an email merge that gives them the promo code for their organization and i want to send it to all the emails i have for each organization.

 

[Razorguns]

Look for the indian guy in your office. Ask him.

r

 

[stilleto]

Look for the indian guy in your office. Ask him.

r

the sad thing is, i'm supposed to be the software expert. i'm usually okay, but i don't know pivot tables. i think i can figure it out though. i have used them.

 

[Cutiebaby]

Ask Lestat he seemed to know what he was talking about when it came to Excel.