If by this you mean have a filter available on the top row of each column ā¦then that is there alright
And you donāt have a complex array formula anywhere in the range? SUMIF or anything?
Do you have the values summed at the bottom by any chance?
Unless you are willing to post up an example of the problem and the formulas used in each column Iām fairly stumped.
Iām not sure how you can copy and paste values only into another sheet and still have the problem TBH. Thatās just numbers and words, so you should of course be able to sort them in any order you need, so something is wrong there. Hidden columns with formulas written in them or something
Got it fixed there by a power excel user. It took him a while but appears there was a v lookup that I had done and made a balls of it ā¦it was giving me the right answer but by accident rather than design. Iām none the wiser tbh but itās fixed now
I have a file with a unique identifier which is a location
I have another file with the same unique identifier but there is multiple rows with the unique identifier
I want to do a v lookup (or similar) that will match the identifiers and pull the detail from multiple rows in file b into one cell on file a
Are you pulling multiple unique identifiers or just one?
Is the identifier all in the wan row or mixed accross columns
And you want to sum them all up in one cell is it?
Multiple unique identifiersā¦customer numbers
But each customer number has multiple rows next to their number in file 2 each with text that I want to pull into one cell on file 1
So for eg
File 1 I have customer no 1234
File 2 I have customer no 1234
Cell 1 = cust no
Ćell 2 = red
On another row then I have
Cell 1 = cust no
Ćell 2 = blue
What I want then is in file 1 to v look up all customer numbers and in a cell 1 have customer no and cell 2 have red,blue
This a one off job or something you will have to do again?
To achieve your goal of consolidating the details from multiple rows in File B into one cell in File A based on a matching unique identifier, you can use a combination of Excel functions such as VLOOKUP, INDEX, and CONCATENATE.
Hereās a step-by-step guide:
-
Open both File A and File B in Excel.
-
Identify the unique identifier column in both files. Letās assume itās column A in both files.
-
In File A, create a new column (letās say column B) where you want to consolidate the details from File B.
-
In cell B2 of File A, enter the following formula:
=IFERROR(CONCATENATE(VLOOKUP(A2, 'File B'!A:B, 2, FALSE), ", ", INDEX('File B'!B:B, MATCH(A2, 'File B'!A:A, 0))), "")This formula combines the VLOOKUP function and the INDEX function to retrieve the details from File B and concatenate them into one cell. It assumes that File B is in a separate sheet named āFile Bā. Adjust the sheet name and column references as necessary.
-
Drag the formula down to apply it to all the cells in column B of File A.
The formula searches for the unique identifier in File A (cell A2) in the unique identifier column of File B (āFile Bā!A:A). It uses the VLOOKUP function to retrieve the value from column 2 (āFile Bā!A:B) of File B based on the matching identifier. Then, it concatenates the retrieved value with additional details from column B (āFile Bā!B:B) using the INDEX and MATCH functions. If no match is found, the IFERROR function ensures that an empty string is displayed instead of an error.
Make sure to adjust the sheet name and column references in the formula based on your specific file setup.
1 Like
One off
Well you have the answer above.
But if thatās a bit complicated I would just do a vlookup for each colour,
Customer id in cell A,
Cell B,vlookup, if thereās a red, then red appears, if not then blank.
Cell C, same for blue
Etc etc
Then you can concatanate at the end of the row, and hide the cells in the middle.
Just makes it easier to find where the problems are if you arenāt used to it.
1 Like
I painstakingly followed the detail above and it didnāt workā¦il try again later
Thatās even more complicated pal 
Take it up with ChatGPT.
Youād be mad not to use it for these type of queries.
A fella that canāt work excel hasnāt much of a chance with chat gpt
Put your post above into it.
Tell it the error you got after trying it, same as if you were posting on here, and it should give you suggestions to fix it.
1 Like
All these excel jockeys should be gone now with ChatGPT.
2 Likes
If he was brave enough and posted the spreadsheet into chatgpt, he could just ask it for the answer in English
Thatās the future
Not very GDPR compliant though
1 Like
This might sound a bit complicated but is actually fairly straightforward. Save beforehand anyway.
Click on a blank cell in your sheet in file B. Go to Insert Table, reference your range in file B and click on headers (assuming you have headers).
Your data should now look like a table. Right click - Get Data from Table/Range. That will open a Power Query.
Click on Transform tab - Group By.
Choose Column A (or whatever has your unique id) in first box.
Then fill in
New Column Name - call it something
Operation - Sum
Column - B (or whatever has your red, blue)
That should give you an error. In the code, where it says List.Sum ([B]), change it to Text.Combine ([B], ā,ā), and click enter. Table should look like what you want now.
Home - Close&LoadTo New Worksheet. That should put a new table back into your file. If you update the file it will refresh if you right click the table and refresh. Can vlookup from there into file A.
Legend Jahan 
1 Like
Il ask one more so while Iām at it (chat gpt gave me 2 responses neither worked )
Iv done the initial work as explained by Jahan
In some of the consolidated cells there are duplicate words.
Iād like to remove the duplicate words in any given cell. But allow the words to exist in multiple cells in a given column