Consolidate 2 columns of data

[#1]

I have a table structure set up in the following: Headings: Furnace, Furnace 1, Furnace 2. Rows: Brand Name, Brand name 1, Brand name 2. I would like to consolidate the furnace columns to 1 name such as Heating. Then be able to give a distinct count of each brand name. My result would be...User can choose Heating in a list box...then choose a specific brand name and see a distinct count of those brands. I made an "if" statement to create the Heating field, but it's not pulling any data for that field. Any help is greatly appreciated.

Cody Smith | IT Data Analyst

email: wsmith@ahslink.com<mailto:swoodward@ahslink.com>

office: 901-597-8126

889 RidgeLake Blvd, Memphis, TN 38120 | www.ahs.com<http://www.ahs.com/>

CONFIDENTIALITY NOTICE:

The information contained in this e-mail, including any attachment(s),

is confidential information that may be privileged and exempt from

disclosure under applicable law. If the reader of this message is not

the intended recipient, or if you received this message in error, then

any direct or indirect disclosure, distribution or copying of this

message is strictly prohibited. If you have received this message in

error, please notify the ServiceMaster Help Desk immediately by calling

(866) 597-4321 and by sending a return e-mail; delete this message; and

destroy all copies, including attachments.

Thank you.

[#2]

William, could you please clarify your task and attach your *.qvw? Distinct count of brands will always be equal to 1 if user selects specific brand.

[#3]

Thanks for the response. Below is an example of the Table structure

Furnace brand 1

Furnace brand 2

Furnace brand 3

Amana

Goodman

whirlpool

GE

Amana

GE

Goodman

GE

What I would like to do is combine the Furnace columns into 1 column..but allowing the brand fields to duplicate so I don’t lose my count. So from the example above..the result should show as a list box:

State

Zip

HTG (would filter for only the brands listed in the 3 columns above)

Amana (would give a count of times Amana shows up under furnace)

This would be done for Appliances, A/C, etc….

My ultimate goal is to give the end user the ability to click State, Zip, Item, and Brand in a list box..which would ultimately show them how many items with that brand name shows up in that zip/state. This would show them that we have for example..500 Amana units in zip 38060, so we need to make sure we have contractors that specialize in Amana equip for that area. Zip 38048 might only show 3, so it’s not crucial to have contractors in that area.

[maxgro]

see attachment

just to understand if I'm going in the right direction