Find Multiple values per Item without duplicates

[Jongo1]

I was using TEXTJOIN(", ",TRUE,IF(A2=Data!E:E,Data!H:H,"")) but this formula provides all the values even if it repeats. I'm looking to remove the duplicate values and only display the unique values.

Basically I have specific items(text) associated with 1 of 70 numbers in a row. Each item should only be associated with 1 of those numbers, but I want to find the anomaly's that have more than 1 number per item. Then I want to display the numbers that each item is associated with.

I'm trying to do a comparitive analysis to see what is the correct number to be using. If there is a simpler way as well let me know. Like in a pivot table that just show items that have multiple numbers and exclude the ones that don't.

 

[Psygrrl88]

so, im not sure how your data is set up, but the simplest solution to me seems to be to sort the items by name and see which ones have multiple numbers?? using filter/sort?

 

[Jongo1]

Theres 200K rows of data some items have 100's of rows so its not practical to scroll through that way. My end goal is to set a pivot with items that have multiple values, so its easy for my team to read and adjust.

 

[Peter_SSs]

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’)

Then perhaps you could make up a small set of dummy data and show us that* with the expected results included and explain again in relation to the sample data.

* MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Psygrrl88]

out of curiosity, do you have a list of the correct numbers for each item by chance? or would have have to manually look that up for each item?

 

[Jongo1]

excel from office 16.

I do have a list, but the item and the list do not correlate to one another. The items are labeled by what fund/bank type/support type they belong to so its basically a manual thing. We send out communication on how they should be set when the items pop up. The same item may be associated with different fund/bank/support etc. Most items are correctly classified since we communicate it out, but we have 50 markets that can go rogue and enter the wrong number to correlate to the item. This is why I want to compare what # is associated with what so it makes it easier for us to pick the correct value.

 

[Jongo1]

Site State	Corp Supplier Name	Corp Product Sub-Group Name	Corp Item Description	Corp Item Nbr & Description	Corp Item Size	Posting Period	Group ID	textjoin
WA	Wine	Subgroup	Kend	970134 - KEND 18	750	202110	934	934 & 934 & 934 & 928
WA	Wine	subgroup	Kend	970134 - KEND 18	750	202110	934
WA	Wine	subgroup	Kend 19	990718 - 19	750	202110	934

Basically need anything associated with Corp Item NBR & Description to show me all the unique values for the group id

 

[Peter_SSs]

excel from office 16.

I'm not sure that is clear since Excel 2016 does not have the TEXTJOIN function. Perhaps you are referring to the folder name where the application files are held?
In Excel if you go to File -> Account perhaps you see Microsoft 365? Or perhaps Excel 2019 or 2021 information?

In any case, please add the Excel version to your account details as I described above so that it is visible all the time.

From your very small sample data it is unclear how 934 & 934 & 934 & 928 was arrived at. Can you post some slightly larger sample data, with expected results, and explain how those results would be obtained manually?