Adding rows based on comma value

smashclash · Feb 16, 2020

I have a set of data like below that I need to separate out. Rather than the data be broken out for each single instance of a name it's grouped together at times and summed the amt. So for example in the below picture I'd like Chip Dale and Vicky Micky to be in separate rows and each have an amount of $500. VBA could identify the need to break this out because of the comma in column A. Frank Tank in A3 would be no action since there are no commas. Chip Dale, Vicky Micky, Mike Ike in A5 would need two rows added and the 800 divided by 3 (266.67) since there are three names for the Amt.

For all the rows with commas the names should only appear one time after VBA runs. So it could work as follows. Search column A for a comma. FInd comma and insert the number of rows equivalent to the number of commas. Then adjust the Names so they only appear once. Example, search column A for comma, find one in Column A2. Clone row 2. Change name in row A2 to Chip Dale and amount to be 500. The new row added would be Vicky Micky and Amt is 500. Then repeat for any other commas in column A.

sandy666 · Feb 17, 2020

something like this?



Year	Group	Brand	Product Line	ID	Advertiser_ID	Advertiser_Name	Brand_ID	Brand_Name	Line_ID	Website_ID	Start_Date	End_Date	LineType	Currency_Code	Rate	Estimated_Qty	Estimated_Amount	Actual_Qty	Actual_Amount	Is_RON	Qty_Served	Qty_Server_AsOf	AuditStamp	Website_Name	External_ID	External_OrderID	Agency_Name	Status	LineDescription	SalesReps	IsNewsletter	EstimatedNet	ActualNet	FCEstimatedNet	FCActualNet	FCEstimatedAmount	FCActualAmount	ProposalID	ContractID	Sizes	RONWebsiteID	RONWebsiteName	SectionID	SectionName	PositionID	PositionName	HasPrePayment	CampaignType	GLTypeID	GLTypeName	TrackingCurrencies	RatecardRate	MaterialStatusCode	AdTypeID	AdTypeName	ProductionControllerID	ProductionControllerName	CampaignTypeDescription	TCEstimatedNet	TCActualNet	TCEstimatedAmount	TCActualAmount	ToolTip	Selected	AdServerID	ThirdPartyID	FourthPartyID	UDFAnswers	CampaignDescription
2020 YTD	Promo	Promo	Live Event	100	102434	Gilbert	XX	Gilbert	251	10040901	01/11/2019	01/11/2019	FF	USD	5950	1	6703.2	1	6703.2	FALSE	0		1.89186E+12	Promo DC				IS	Standard Sponsorship	VickyMicky	TRUE	6703.2	6703.2	6703.2	6703.2	6703.2	6703.2			Standard Sponsorship							FALSE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	5950		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	MM.9921
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5193	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	JesseCoat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5193	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	VickyMicky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5194	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	JesseCoat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5194	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	VickyMicky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5195	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	JesseCoat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5195	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	VickyMicky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5196	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	JesseCoat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5196	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	VickyMicky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	TM	NPP	Video	1015	102319	Software	XX	Software	5259	10210401	01/11/2019	01/11/2019	FF	USD	5500	1	5500	1	5500	FALSE	0		1.89684E+12	NPP Video				IS	Testimonial Video	ChrisTophe	TRUE	5500	5500	5500	5500	5500	5500			Testimonial Video							FALSE	M	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Performance	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PI	Print	1016	103217	Copier	A1	Copier	5265	10010301	15/11/2019	15/11/2019	FF	USD	4500	1	4500	1	4500	FALSE	0		1.90013E+12	PI the Mag			Wavemaker Global	IS	Full Page	ChrisPilgrim	FALSE	4500	4500	4500	4500	4500	4500			Full Page							FALSE	M	1001	Print	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	7875		1	ROP	N.Support	N.Support	Performance	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Fall Advertising
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	ChrisPilgrim	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	ChrisTophe	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	LaurenLilly	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5268	10250901	08/11/2019	08/11/2019	FF	USD	1000	1	1000	1	1000	FALSE	0		1.89684E+12	PKI DPS				IS	Sponsor Additional Staff Badge	ChrisPilgrim	TRUE	1000	1000	1000	1000	1000	1000			Sponsor Additional Staff Badge							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	2500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	ChrisPilgrim	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	ChrisTophe	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	LaurenLilly	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]

smashclash · Feb 17, 2020

Yes! Only thing missing is the space in between the first and last name in the SalesRep column.

sandy666 · Feb 17, 2020

check if this is ok



Year	Group	Brand	Product Line	ID	Advertiser_ID	Advertiser_Name	Brand_ID	Brand_Name	Line_ID	Website_ID	Start_Date	End_Date	LineType	Currency_Code	Rate	Estimated_Qty	Estimated_Amount	Actual_Qty	Actual_Amount	Is_RON	Qty_Served	Qty_Server_AsOf	AuditStamp	Website_Name	External_ID	External_OrderID	Agency_Name	Status	LineDescription	SalesReps	IsNewsletter	EstimatedNet	ActualNet	FCEstimatedNet	FCActualNet	FCEstimatedAmount	FCActualAmount	ProposalID	ContractID	Sizes	RONWebsiteID	RONWebsiteName	SectionID	SectionName	PositionID	PositionName	HasPrePayment	CampaignType	GLTypeID	GLTypeName	TrackingCurrencies	RatecardRate	MaterialStatusCode	AdTypeID	AdTypeName	ProductionControllerID	ProductionControllerName	CampaignTypeDescription	TCEstimatedNet	TCActualNet	TCEstimatedAmount	TCActualAmount	ToolTip	Selected	AdServerID	ThirdPartyID	FourthPartyID	UDFAnswers	CampaignDescription
2020 YTD	Promo	Promo	Live Event	100	102434	Gilbert	XX	Gilbert	251	10040901	01/11/2019	01/11/2019	FF	USD	5950	1	6703.2	1	6703.2	FALSE	0		1.89186E+12	Promo DC				IS	Standard Sponsorship	Vicky Micky	TRUE	6703.2	6703.2	6703.2	6703.2	6703.2	6703.2			Standard Sponsorship							FALSE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	5950		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	MM.9921
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5193	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5193	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5194	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5194	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5195	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5195	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5196	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5196	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	TM	NPP	Video	1015	102319	Software	XX	Software	5259	10210401	01/11/2019	01/11/2019	FF	USD	5500	1	5500	1	5500	FALSE	0		1.89684E+12	NPP Video				IS	Testimonial Video	Chris Tophe	TRUE	5500	5500	5500	5500	5500	5500			Testimonial Video							FALSE	M	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Performance	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PI	Print	1016	103217	Copier	A1	Copier	5265	10010301	15/11/2019	15/11/2019	FF	USD	4500	1	4500	1	4500	FALSE	0		1.90013E+12	PI the Mag			Wavemaker Global	IS	Full Page	Chris Pilgrim	FALSE	4500	4500	4500	4500	4500	4500			Full Page							FALSE	M	1001	Print	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	7875		1	ROP	N.Support	N.Support	Performance	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Fall Advertising
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	Chris Pilgrim	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	Chris Tophe	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	Lauren Lilly	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5268	10250901	08/11/2019	08/11/2019	FF	USD	1000	1	1000	1	1000	FALSE	0		1.89684E+12	PKI DPS				IS	Sponsor Additional Staff Badge	Chris Pilgrim	TRUE	1000	1000	1000	1000	1000	1000			Sponsor Additional Staff Badge							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	2500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	Chris Pilgrim	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	Chris Tophe	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	Lauren Lilly	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]

smashclash · Feb 17, 2020

Looks like it's putting a leading space on the Salesrep names to the rows that are added. Anyway to remove that?

sandy666 · Feb 17, 2020

Now?



Year	Group	Brand	Product Line	ID	Advertiser_ID	Advertiser_Name	Brand_ID	Brand_Name	Line_ID	Website_ID	Start_Date	End_Date	LineType	Currency_Code	Rate	Estimated_Qty	Estimated_Amount	Actual_Qty	Actual_Amount	Is_RON	Qty_Served	Qty_Server_AsOf	AuditStamp	Website_Name	External_ID	External_OrderID	Agency_Name	Status	LineDescription	SalesReps	IsNewsletter	EstimatedNet	ActualNet	FCEstimatedNet	FCActualNet	FCEstimatedAmount	FCActualAmount	ProposalID	ContractID	Sizes	RONWebsiteID	RONWebsiteName	SectionID	SectionName	PositionID	PositionName	HasPrePayment	CampaignType	GLTypeID	GLTypeName	TrackingCurrencies	RatecardRate	MaterialStatusCode	AdTypeID	AdTypeName	ProductionControllerID	ProductionControllerName	CampaignTypeDescription	TCEstimatedNet	TCActualNet	TCEstimatedAmount	TCActualAmount	ToolTip	Selected	AdServerID	ThirdPartyID	FourthPartyID	UDFAnswers	CampaignDescription
2020 YTD	Promo	Promo	Live Event	100	102434	Gilbert	XX	Gilbert	251	10040901	01/11/2019	01/11/2019	FF	USD	5950	1	6703.2	1	6703.2	FALSE	0		1.89186E+12	Promo DC				IS	Standard Sponsorship	Vicky Micky	TRUE	6703.2	6703.2	6703.2	6703.2	6703.2	6703.2			Standard Sponsorship							FALSE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	5950		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	MM.9921
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5193	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5193	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5194	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5194	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5195	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5195	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5196	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Jesse Coat	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	Promo	PM	Video	1002	102590	KTI	XX	KTI	5196	10240401	01/11/2019	01/11/2019	FF	USD	500	1	500	1	250	FALSE	0		1.89684E+12	PM Video				IS	Product Video	Vicky Micky	TRUE	500	500	500	500	500	500			Product Video							FALSE	F	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Product Video Series 1
2020 YTD	TM	NPP	Video	1015	102319	Software	XX	Software	5259	10210401	01/11/2019	01/11/2019	FF	USD	5500	1	5500	1	5500	FALSE	0		1.89684E+12	NPP Video				IS	Testimonial Video	Chris Tophe	TRUE	5500	5500	5500	5500	5500	5500			Testimonial Video							FALSE	M	1620	Video	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	50000		13	Video-Flat Fee	N.Support	N.Support	Performance	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PI	Print	1016	103217	Copier	A1	Copier	5265	10010301	15/11/2019	15/11/2019	FF	USD	4500	1	4500	1	4500	FALSE	0		1.90013E+12	PI the Mag			Wavemaker Global	IS	Full Page	Chris Pilgrim	FALSE	4500	4500	4500	4500	4500	4500			Full Page							FALSE	M	1001	Print	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	7875		1	ROP	N.Support	N.Support	Performance	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]	Fall Advertising
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	Chris Pilgrim	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	Chris Tophe	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5267	10250901	08/11/2019	08/11/2019	FF	USD	13500	1	13500	1	4500	FALSE	0		1.89684E+12	PKI DPS				IS	Silver Sponsorship	Lauren Lilly	TRUE	13500	13500	13500	13500	13500	13500			Silver Sponsorship							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	13500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5268	10250901	08/11/2019	08/11/2019	FF	USD	1000	1	1000	1	1000	FALSE	0		1.89684E+12	PKI DPS				IS	Sponsor Additional Staff Badge	Chris Pilgrim	TRUE	1000	1000	1000	1000	1000	1000			Sponsor Additional Staff Badge							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	2500		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	Chris Pilgrim	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	Chris Tophe	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]
2020 YTD	Print	PKI	Live Event	1017	106975	Significant	XX	Significant	5272	10250901	08/11/2019	08/11/2019	FF	USD	350	1	350	1	116.67	FALSE	0		1.89684E+12	PKI DPS				IS	Golf - Play Golf	Lauren Lilly	TRUE	350	350	350	350	350	350			Golf - Play Golf							TRUE	F	1221	Sponsorships	System.Collections.Generic.List`1[Elan.Library.Ad.Internet.OrdersByWebsite+TrackingCurrency]	350		23	Sponsorships	N.Support	N.Support	Flexible	0	0	0	0		FALSE				System.Collections.Generic.List`1[System.String]

smashclash · Feb 17, 2020

Looks good now. Thank you!!!

How can I pull the code from your post to test it on my end?

sandy666 · Feb 17, 2020

Because I don't know your Excel version, I assumed this is XL 2016 or higher and you can use Power Query aka Get&Transform
here is M-code

Code:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Table.Transpose(Table.DemoteHeaders(Source)), "Index", 1, 1),
    Count = Table.AddColumn(Table.TransformColumnTypes(Table.PromoteHeaders(Table.Transpose(Index), [PromoteAllScalars=true]),{{"SalesReps", type text}}), "Count", each List.Count(Text.Split([SalesReps],","))),
    Replace = Table.ReplaceValue(Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Count, "Division", each [Actual_Amount] / [Count], type number),{"Actual_Amount", "Count"}),{{"Division", "Actual_Amount"}})," "," ",Replacer.ReplaceText,{"SalesReps"}),
    Sort = Table.Sort(Table.TransformColumnTypes(Table.Transpose(Table.DemoteHeaders(Replace)),{{"Column12", Int64.Type}}),{{"Column12", Order.Ascending}}),
    Round2 = Table.TransformColumns(Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(Sort,{"Column12"})), [PromoteAllScalars=true]),{{"Actual_Amount", each Number.Round(_, 2), type number}}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Round2, {{"SalesReps", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "SalesReps"),
    Type = Table.TransformColumnTypes(Split,{{"Start_Date", type date}, {"End_Date", type date}})
in
    Type

and this is NOT vba

smashclash · Feb 17, 2020

Edit.

sandy666 · Feb 17, 2020

1. update your profile about Excel version (Account details)
2. check if your version contain Power Query / Get&Transform
3. did you read last line of the previous post?

smashclash · Feb 17, 2020

I'm on Office 365 for business. I will get my IT guy to add Power Query. Because of security settings it will not allow me to do it.

Adding rows based on comma value

smashclash

Board Regular

sandy666

Banned - Rules violations

Excel Facts

smashclash

Board Regular

sandy666

Banned - Rules violations

smashclash

Board Regular

sandy666

Banned - Rules violations

smashclash

Board Regular

sandy666

Banned - Rules violations

smashclash

Board Regular

sandy666

Banned - Rules violations

smashclash

Board Regular

Similar threads

Forum statistics

Share this page

Adding rows based on comma value

Board Regular

Banned - Rules violations

Excel Facts

Board Regular

Banned - Rules violations

Board Regular

Banned - Rules violations

Board Regular

Banned - Rules violations

Board Regular

Banned - Rules violations

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock