Grouped dataset to unique rows with muliple columns 2

[jjb373]

All - I am not sure that my subject line explains my situation but here is the scenario. I have a list of companies and for each company a list of sales contacts who the company works with. So, one company may have just one sales contact or many sales contacts.

I have an Excel sheet which contains 2 columns(fields):
CompanyID SalesContact
1 Amber
Bill
Carlos
2 Bill
3 Carlos
4 Amber
Bill
5 Carlos

What I want to do is take the layout I currently have and display one row for each company. So, take the one to many relationship I currently have and display it as follows:

CompanyID Sales1 Sales2 Sales3
1 Amber Bill Carlos
2 Bill
3 Carlos
4 Amber Bill

Thank you in advance for your time and suggestions.

-- JJB373 --

 

[GlennUK]

Have an extra column in the source data, made up of the company id and the sales person number, like this
CompanyID1Sales1
CompanyID1Sales2
CompanyID1Sales3
CompanyID2Sales1
CompanyID3Sales1
CompanyID4Sales1
CompanyID4Sales2
CompanyID5Sales1
... this can be done with fairly simple formulas.

Then in your new layout type the company ids down the left hand side of the layout, and the sales refs across the top of the layout, and use this formula in the actual layout body:

=IF(ISNA(MATCH("CompanyID"&$G2&H$1,$C$1:$C$9,0)),"",INDEX($B$1:$B$9,MATCH("CompanyID"&$G2&H$1,$C$1:$C$9,0)))

... this is using G2 as the company number ( top of left hand side of layout ), and H1 as the ref to the sales ref ( left-most of top row of layout sales refs ), so adjust as required. Note the position of the $ in these references ... the formula can then be copied for the body of the new layout.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[jjb373]

Glenn - thank you for the reply. I gave this a try with my dataset but I ran into a few problems. I have roughly 3000 unique companies and roughly 125 sales reps. One company can have up to 5 different sales reps from the list of 125. Note: each company is required to have at least one.

So, I was not able to follow the solution you provided since the columns containing sales reps for each company may be any one of the 125 sales reps.

Basically I want the end result to be a unique list of companies (3000+ rows) and the next 5 columns (columns B thru F) to contain place holders for a sales rep if one exists based on another list I have in a different tab.

Maybe I should have stated that I am working off of 2 lists. Sheet 1 is a unique list of companies and sheet 2 is a grouped list of those same companies in column A and the sales reps associated to that company in column B. So, sheet 2 looks like the original list I provided in my first post:

Company SalesRep
1 Mike
1 Amber
1 Bill
2 Mike
3 Mike
3 Bill
4 Susan

Please let me know your thoughts.


-- JJB373 --

 

[jjb373]

Basically what I am trying to do is "pivot" the list of sales reps from sheet 2 and append them to new columns within sheet 1 and if a company has more than 1 sales rep, the 2nd sales rep falls into the next column named salesrep2, and so forth up to the 5 columns which could contain the list of sales reps.

If this format can be done more efficiently using MS Access please provide input as I can import the files into that program too.

-- JJB373 --

 

[SkipVought]

list your company id's in column E starting in row 2.

insert a list starting with 1 and incrimenting by 1 in row 1, starting in column F...
[tt]
1 2 3 4 5 6 ... as many as you need...
1
2
3
4
5
[/tt]
In F2 the formula...
[tt]
F2: ==IF(COUNTIF($A:$A,$E2)<F$1,"",INDEX(OFFSET($A$1,MATCH($E2,$A:$A,0)-1,1,COUNTIF($A:$A,$E2),1),F$1))
[/tt]
copy down and across to fill the matrix.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjb373]

Skip - your solution works great. Thank you very much!

-- JJB373 --

 

[GlennUK]

Nice formula Skip ===> *





Cheers, Glenn.

Beauty is in the eye of the beerholder.