carrrrlitos17
New Member
- Joined
- Jul 7, 2022
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
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
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
