My data doesn't automatically continue when I add info into its associated table

[carrrrlitos17]

Hello again,

I find myself with two problems.

Context: I have a table that lists out what each company supplies. If there are multiple products for a company, the company is relisted plus all the contact info and thus, it can get a little repetitive (See img. Table). To combine the info, I had to make that last column to list the combined info, with the function being =COUNTIF($E$2:E2,E2)&":"&TEXTJOIN("|",,D2,B2)

I then made another sheet where companies can be listed and each of their products be listed all in one row, followed by their contact info (so each company only has one row not multiple) (See img Current new sheet).

Continuing to look at that image from left to right, my functions in each Column are as follows:

=UNIQUE(Table2[Company])

=TEXTJOIN(CHAR(10),,FILTER(Table2[For Table],Table2[Company]=A22))

=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table2[Contact],Table2[Company]=A22," ")))


I have noticed that when I add a new line(s) to the original table, my process becomes sloppy.

Problem 1) In the table, my last column function seems to lose track and appears like this (see New Table) I notice that the function on what had once been the last row changes from being =COUNTIF($E$2:E261,E261)&":"&TEXTJOIN("|",,D261,B261)
to
=COUNTIF($E$2:E263,E261)&":"&TEXTJOIN("|",,D261,B261)

The problem lies in the range area of the COUNTIF, how do I allow it to keep updating forward?

Problem 2) (see problem 2) the only thing that carries over is the Unique function that lists the company, but the other columns dont fill in.

All in all, my goal is to be able to add new companies to my table and have that data be added to my other sheet without a problem.

Thank you, sorry for the wordy explanation

 

[RoryA]

Change your formulas to use structured referencing - you'll probably want to use INDEX

 

[carrrrlitos17]

Change your formulas to use structured referencing - you'll probably want to use INDEX

Thank you for responding, I thought I had structured referencing in my formula. How would you add Index?

 

[RoryA]

This:

=COUNTIF($E$2:E261,E261)&":"&TEXTJOIN("|",,D261,B261)

is not structured referencing. What are the names of the table columns for column E, D and B?

 

[carrrrlitos17]

This:

=COUNTIF($E$2:E261,E261)&":"&TEXTJOIN("|",,D261,B261)

is not structured referencing. What are the names of the table columns for column E, D and B?

Oh I see, the column names are Company, Product/Service, and Type, respectively

 

[RoryA]

Try:

=COUNTIF(INDEX([Company],1):[@Company],[@Company])&":"&TEXTJOIN("|",,[@[Product/Service]],[@Type])

 

[carrrrlitos17]

Try:

=COUNTIF(INDEX([Company],1):[@Company],[@Company])&":"&TEXTJOIN("|",,[@[Product/Service]],[@Type])

That seems to do the trick for the Table WorkSheet, thank you so much! As for the second question, I am still having difficulty with it.

If you take a look at Img 1, this is how it currently updates, but as you can see the For Table info (that being the info you just helped me with) nor does the contact info (contact name, email, and phone number) carry over. In the Table Test pic you can see how it currently shows up on the table. How can I have it so the table info goes to the other sheet?

My Formulas from Left to right
=TEXTJOIN(CHAR(10),,FILTER(Table2[For Table],Table2[Company]=A164, " "))
=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table2[Contact],Table2[Company]=A164," ")))
=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table2,Table2[Company]=A164," ")))
=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table2[Phone Number],Table2[Company]=A164," ")))

 

[RoryA]

It doesn't look like the data in the first image is in a table, so you need to use dynamic array formulas on that sheet in order for them to automatically spill down. It would be easier to help if you could either post a link to a file or use XL2BB to post samples of data that we can actually work with, rather than pictures.

 

[carrrrlitos17]

Here is the Table

For Excel Site.xlsx
	A	B	C	D	E	F	G	H
1
2	System	Type	Product/Service	Company	Contact	Email	Phone	For Table
3	Internal System	supplier	Puzzles	Company A	Steve	Steve@gmail.com	78093174	1:Puzzles|supplier
4	Internal System	supplier	Dominoes	Company B	Karlo	Karlo@gmail.com	12347098	1:Dominoes|supplier
5	Internal System	supplier	Puzzles	Company B	Karlo	Karlo@gmail.com	12347098	2:Puzzles|supplier
6	Internal System	Installer	Jacks	Company C	Abe	Abe@gmail.com	43287013	1:Jacks|Installer
7	Internal System	Supplier	Marbles	Company D	Trisha	trisha@gmail.com	5324528	1:Marbles|Supplier
8
9
Table

Cell Formulas
Range	Formula
H3:H7	H3	=COUNTIF(INDEX([Company],1):[@Company],[@Company])&":"&TEXTJOIN("|",,[@[Product/Service]],[@Type])

And Here is the sheet I want to have updated. As you can see, Company D with contact Trisha was recently inserted into this table, but it won't update on the second sheet.

For Excel Site.xlsx
	A	B	C	D	E	F
1
2	Supplier	Goods/Services	Contact	Email	Telephone
3	Company A	1:Puzzles|supplier	Steve	Steve@gmail.com	78093174
4	Company B	1:Dominoes|supplier 2:Puzzles|supplier	Karlo	Karlo@gmail.com	12347098
5	Company C	1:Jacks|Installer	Abe	Abe@gmail.com	43287013
6	Company D
7
Sheet2

Cell Formulas
Range	Formula
A3:A6	A3	=UNIQUE(Table1[Company])
B3:B5	B3	=TEXTJOIN(CHAR(10),,FILTER(Table1[For Table],Table1[Company]=A3, " "))
C3:C5	C3	=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table1[Contact],Table1[Company]=A3," ")))
D3:D5	D3	=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table1[Email],Table1[Company]=A3," ")))
E3:E5	E3	=TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Table1[[Phone ]],Table1[Company]=A3," ")))
Dynamic array formulas.

 

[RoryA]

Do you have BYROW and LAMBDA functions available to you?