Which function should I use? Please advise

[excellearner1298]

Hello, I am relatively new to the repertoire of functions in Excel and need your suggestions. Anything will help! I need to build this set as soon as possible.



Project: Building a dataset based on US Imports. Every product has a unique identifyer (product code).

If a product qualifies for several programs, the original data set repeats the product code along with every program. The data looks something like this:

code 1 program "a" amount$
code 1 program "b" amount$
code 2 program "a" amount$
code 2 program "c" amount$
code 3 program "a" amount$

I need a column with a variable/ code that will let me know which entry program each product used. I want a dataset that looks like this:

code 1 program "a and b" amount$
code 2 program "a and c" amount$
code 3 program "a" amount$

or something like this

code 1 2 amount$
code 2 3 amount$
code 3 1 amount$

where 1=program a, 2= program a and b, and 3= program a and c


I was considering Vlookup but don't think this will work because one code is repeated several times. The other function I was thinking about was Concatenate- "concatenate the column that lists the program name if the code is repeated" and "sum if the code is repeated".



Thank you for your help!

 

[JackDanIce]

Difficult to understand your requirements, if you could include a screenshot with actual examples it would help alot, e.g. column and row details, actual data content etc.

If you're using Excel 2007 or later, the SUMIFs function is likely to return what you want.

SUMPRODUCT probably can too and would suggest using that if you're on Excel 2003 or earlier.

 

[excellearner1298]

Hello and thank you for your answer.

Here is an example of format the data is in right now

[TABLE="width: 500"]
<tbody>[TR]
[TD]code [/TD]
[TD]program code [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]03023400[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD]No program claimed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]03033901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD]Andean Act (ATPA) [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]03033901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GSP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]07108070[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD]Andean Act (ATPA) [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]07108070[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GSP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]07102120[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GSP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is what I want

[TABLE="width: 500"]
<tbody>[TR]
[TD]code [/TD]
[TD]program code [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]03023400[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD]No program claimed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]03033901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD]Andean Act (ATPA) & GSP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]07108070[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD]Andean Act (ATPA) & GSP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]07102120[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GSP[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Once I have this set up I will use v look up get the amount from another set. I am sorry if the last idea in my post was confusing. I meant to convey that I don't need the programs to be spelled out, I mentioned this because working with string text might present difficulties. If it is easier to work with a code, as opposed to the program name, I have no problem doing that.

thank you very much for your advice