Summarize Data from Underlying Sheets Using JoinText

torourke17

New Member
Joined
Jan 12, 2018
Messages
12
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,

I have a formula that is able to look at an underlying sheet and summarize a variety of exception codes to a single summary sheet. The formula excludes duplicate exception codes. If there are no exception codes it returns a "-". The issue I'm running into is that each day, the range of results can change. It could be K17:K23 one day, but it could be K17:K100 the next day, the data will always start in K17. Instead of having to adjust the formula every day, I want the formula to grab any values within column K, but when I do K:K, it returns an error. Below is a formula and attached is a picture of what I'm working with. Further, I want to be able to take this formula and drag it down to capture a dozen+ different accounts (underlying tabs), where column A = the tab name. Lastly, I'd also like the ability for the formula to excludes the "-" and the column header "Exception Code".

It's a huge ask, but I've found this site to be extremely helpful.

"=TEXTJOIN(", ", TRUE, IF(MATCH('101'!K17:K23, '101'!K17:K23, 0)=MATCH(ROW('101'!K17:K23), ROW('101'!K17:K23)),'101'!K17:K23, ""))"
"=TEXTJOIN(", ", TRUE, IF(MATCH('102'!K17:K23, '102'!K17:K23, 0)=MATCH(ROW('102'!K17:K23), ROW('102'!K17:K23)),'102'!K17:K23, ""))"

1611600561136.png
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (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’)

With your formula
Excel Formula:
=TEXTJOIN(", ", TRUE, IF(LEN('101'!K18:K500)>1,IF(MATCH('101'!K18:K500, '101'!K18:K500, 0)=MATCH(ROW('101'!K18:K500), ROW('101'!K18:K500)),'101'!K18:K500, ""),""))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top