# Convert multiple rows for a company to a single row

1. ## Convert multiple rows for a company to a single row

Hello,

I'm looking for a formula (not a macro) to covert multiple records for a particular company into one row. I actually have over 3000 records but the below sample example gives you an idea of data ..

What I have now..
Organisation Department Funding
Red A \$100,000.00
Red B \$200,000.00
Red C \$150.00
Red D \$0.00
Blue A \$5,000.00
Blue B \$10,000.00
Blue C \$750,000.00
Blue D \$5,000.00

What I need
Organisation Funding_A Funding_B Funding_C Funding_D
Red \$100,000.00 \$200,000.00 \$150.00 \$0.00
Blue \$5,000.00 \$10,000.00 \$750,000.00 \$5,000.00

Many thanks for any assistance you can provide.

Cheers,

2. ## Re: Convert multiple rows for a company to a single row

How do you want the results set out? in seperate columns?
How many colours are there? Does the data duplicate? ie is there more than 1 entry for RED A
might ne better attaching a workbook with a larger sample.
Probably you will need to set up a lot of formulas in each cell using the SUMIFS formula

Cheers
Sean

3. ## Re: Convert multiple rows for a company to a single row

Here you go... Sample.xlsx

Don't forget to click the little star to the left of this post if you feel I helped!

4. ## Re: Convert multiple rows for a company to a single row

Hi Sean,

Data needs to be in separate columns. There are probably over 100 different colours (organisations).
For each organisation there are many departments that allocate funding. I basically need a new column for each departments funding.

sean

6. ## Re: Convert multiple rows for a company to a single row

Cheers TheExcelPhile for your speedy reply .. you are one quick problem solver.

Thanks Sean too for your interest.

7. ## Re: Convert multiple rows for a company to a single row

Hi Chloe!

The formula I have used can be auto-filled for as many colours and departments as needed.

8. ## Re: Convert multiple rows for a company to a single row

Hi TheExcelphile .. As I have over 100 different companies in my real data can I also populate the organisation from the formula?

9. ## Re: Convert multiple rows for a company to a single row

Sure, that should be possible. I'll need a sample sheet to work on, though...

10. ## Re: Convert multiple rows for a company to a single row

sorry very new to this where to do you add files .. not sure if i have permissions to do so

11. ## Re: Convert multiple rows for a company to a single row

Click on the Go Advanced button at the bottom of where you type your reply. Once there, scroll down to Manage Attachments and follow the instructions.

12. ## Re: Convert multiple rows for a company to a single row

Thanks ! Sample attached ...

13. ## Re: Convert multiple rows for a company to a single row

Hi Chloe!

I saw your data. It doesn't matter how many colors you have, just add them to the table and auto-fill the formula. It will work.

I noticed that in your worksheet, "D" is stored as "D ", ie. D with a space in front of it. Be careful about such things.

I've attached a worksheet with the formulas done for you. Copy of sample1.xlsx

Don't forget the star

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1