Excel Question

[Oz007]

Hi all,

I have a list of of information like the below and I wish to veiw the information for ABC company in one row, same for CDF and so rather than in alist. How could I do this as I need to run this report on a weekly basis and would like to downlaod the information and present it like this in the easiest way possible. Or is there anthoer to display this information in a smarter way.

Thanks in advance for your help. Oz

Ser	ABC Company	2021	15	Event 1
Ser	ABC Company	2022	24	Event 2
Ser	ABC Company	2023	16	Event 3
Ser	CDF Company	2021	18	Event 1
Ser	CDF Company	2022	14	Event 2
Ser	CDF Company	2023	13	Event 3
Ser	CDF Company	2024	11	Event 4
Ser	EFG Compnay	2020	16	Event 1
Ser	HIJ Company	2021	19	Event 1
Ser	HIJ Company	2022	27	Event 2

 

[etaf]

how do you want it to look exactly
a Unique() filter() and textjoin may work - IF you have the later version of excel - perhaps update your profile with that info

in G2
=UNIQUE(B2:B11)
in H2
=TEXTJOIN(" / ",,FILTER(C2:E11,B2:B11=G2))

Book8
	A	B	C	D	E	F	G	H	I	J
1							Company	textjoin
2	Ser	ABC Company	2021	15	Event 1		ABC Company	2021 / 15 / Event 1 / 2022 / 24 / Event 2 / 2023 / 16 / Event 3
3	Ser	ABC Company	2022	24	Event 2		CDF Company	2021 / 18 / Event 1 / 2022 / 14 / Event 2 / 2023 / 13 / Event 3 / 2024 / 11 / Event 4
4	Ser	ABC Company	2023	16	Event 3		EFG Compnay	2020 / 16 / Event 1
5	Ser	CDF Company	2021	18	Event 1		HIJ Company	2021 / 19 / Event 1 / 2022 / 27 / Event 2
6	Ser	CDF Company	2022	14	Event 2
7	Ser	CDF Company	2023	13	Event 3
8	Ser	CDF Company	2024	11	Event 4
9	Ser	EFG Compnay	2020	16	Event 1
10	Ser	HIJ Company	2021	19	Event 1
11	Ser	HIJ Company	2022	27	Event 2
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(B2:B11)
H2:H5	H2	=TEXTJOIN(" / ",,FILTER(C2:E11,B2:B11=G2))
Dynamic array formulas.

 

[Oz007]

how do you want it to look exactly
a Unique() filter() and textjoin may work - IF you have the later version of excel - perhaps update your profile with that info

in G2
=UNIQUE(B2:B11)
in H2
=TEXTJOIN(" / ",,FILTER(C2:E11,B2:B11=G2))

Book8
	A	B	C	D	E	F	G	H	I	J
1							Company	textjoin
2	Ser	ABC Company	2021	15	Event 1		ABC Company	2021 / 15 / Event 1 / 2022 / 24 / Event 2 / 2023 / 16 / Event 3
3	Ser	ABC Company	2022	24	Event 2		CDF Company	2021 / 18 / Event 1 / 2022 / 14 / Event 2 / 2023 / 13 / Event 3 / 2024 / 11 / Event 4
4	Ser	ABC Company	2023	16	Event 3		EFG Compnay	2020 / 16 / Event 1
5	Ser	CDF Company	2021	18	Event 1		HIJ Company	2021 / 19 / Event 1 / 2022 / 27 / Event 2
6	Ser	CDF Company	2022	14	Event 2
7	Ser	CDF Company	2023	13	Event 3
8	Ser	CDF Company	2024	11	Event 4
9	Ser	EFG Compnay	2020	16	Event 1
10	Ser	HIJ Company	2021	19	Event 1
11	Ser	HIJ Company	2022	27	Event 2
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(B2:B11)
H2:H5	H2	=TEXTJOIN(" / ",,FILTER(C2:E11,B2:B11=G2))
Dynamic array formulas.

Thank you for you assitance with this much appreciated.

I have 989 lines and when I insert your formula it states #SPILL! from row 2 to 8 and then presents the information.

SER	ABC123	2021​	24	Events - Forums / Conferences	#SPILL!​
SUP	HYJ123	2021​	56	Events - Forums / Conferences	#SPILL!​
SUP	HYJ124	2022​	5	YM	#SPILL!​
SUP	HYJ124	2022​	8	Training	#SPILL!​
SUP	HYJ124	2022​	64	Events - Forums / Conferences	#SPILL!​
SUP	HYJ124	2022​	3	Members	#SPILL!​
SUP	HYJ128	2023​	8	Events - Forums / Conferences	#SPILL!​
SUP	HYJ128	2023​	3	Members	ABC123
SUP	HYJ128	2023​	5	Pledge	HYJ123
SUP	HYJ129	2023​	5	YM	HYJ124
SUP	HYJ128	2022​	16	Events - Forums / Conferences	HYJ128
SUP	HYJ128	2021​	72	Events - Forums / Conferences	HYJ129
SUP	HYJ128	2022​	24	Events - Forums / Conferences	Advent Tools Ltd

if wanted the information currently presented in Colunm H split into seperate cells rather than use text join what would suggest the formual should be?

 

[etaf]

yes all rows below - should be clear - hence why you get spill

in a free empty column
put
=UNIQUE(B2:B11) - change to the number of rows needed

=UNIQUE(B2:B1000)

and then next to it

=TEXTJOIN(" / ",,FILTER(C2:E11,B2:B11=G2)) change to the number of rows needed

=TEXTJOIN(" / ",,FILTER(C2:E1000,B2:B1000=G2))

may need to add <>"" - BUT

Not the G2 - needs to be changed to match the column you have Unique in
OR
just put the formula in free columns G and H as i posted


This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Oz007]

I am using excel 365 and have updated my account deatils thank you.

 

[etaf]

as you have 365 -
i have asked

how do you want it to look exactly

thanks for the reply , now we know 365

IF you have the later version of excel - perhaps update your profile with that info

then my functions should work
did you try ? again as suggested
However what do you want the out put to look like

 

[Oz007]

@etaf perfect legend thank you so much.

 

[etaf]

you are welcome