Hi
I have a system which exports XML files that I need to convert into Excel for reconciliation etc.
The XML table function in Excel works but puts each bit of data on the next line - for example:
Ref Surname Forename
1
Mouse
Mickey
2
Duck...
Hi
I am unsure how to attach an example workbook for my query.
As you can see from the image above, I have Company, which has two data sets associated with it - Product and Category.
I am looking to allow someone to input the product and category then be given a , separated 'list' of...
I'm trying to join several cells containing comma separated values in a table. I use an array formula as below;
={TEXTJOIN(",",TRUE,IF([@[Part Number]]=Table1[Part Number],Table1[Fitment],""))}
The formula works perfectly if all cells contain 255 characters or less. If a cell contains more...
i have been requested to make a simple absence viewer iin our office that is feeding from a report from our HR system and appending that report over the top of the previous one.
Essentially, the report comes down like this :
Name, Unique ID, Office Location, Dept and then absent date.
At...
Hi Guys.
I am trying to find a solution to this.
I have a list of winners and categories they won in.
<tbody>#DAE7F5[/URL] "]
K
L
20
Jimmy John
Singing
21
Billy Bob
Dancing
22
Sue Partridge
Singing
23
Jones Smith
Guitar
24
Blake Boris
Drum
25
John Wellington
Guitar
</tbody>...
Hi all,
Using Excel for Office 365 MSI 16.0.11 x64 version 1902.
I have a scenario where I think TEXTJOIN would work, but I can't figure out how to concatenate several IF statements together.
I have:
- Column A (id) with hundreds of row entries, simple numbers 1-.....
- Column B (category)...
Okay, after working at this for several hours I'm still stumped. I'm using textjoin to display combined values from Col BF that match numbers in BH but I want to exclude the value in the current row. i.e. for row 2, I want to exclude "CP60" from the results in Col BI and display all other...
Need some help changing the following formula to only grab unique values:
{=TEXTJOIN(",",TRUE,IF($A$2:$A$20000=A2,$C$2:$C$20000,""))}
Here's an example of the data (the last column is where this formula goes for each row:
<colgroup><col><col><col><col></colgroup><tbody>
Part Number
Project...
I'm looking to return a single cell of values that show results matched against one column which are then used to collect their own results. Much easier to explain with a display...
<tbody>
Clothing
Colour
Place
Hat
Red
Head
Hat
Yellow
Head
Glove
Red
Hands
Scarf
Blue
Head
Glove...
I am using the new Excel formula " TEXTJOIN" and works great however this formula only works on the new version of office 360. What formula can I replace this with on older version of Excel? Does anyone know? Thanks in advance.
{=TEXTJOIN(",",0,IF(J20:J23>0,H20:H23,""))}
Opened on behalf of jmb1986 who sent me a PM.
Some bookkeeping items first. If you click on the Forum Rules link in my signature, and look at rule 4, it says you should not solicit help via PM. Several reasons for this, partly because if you ask it in a post others can benefit from it, partly...
Dear Experts,
Hope you all will be good.
I want failing subjects result in one cell. Failing criteria is less than 34 marks subject.
Student MATH SCIENCE PHYSICS CHEMISTRY TOTAL RESULT FAILED SUBJECTS
A 50...
Hello,
I have being trying to use the new text join function in a formula with the criteria being to find a search string, and then joining cells that meet that criteria. In short the following is what I am hoping to accomplish when searching for "W" in the second column:
<colgroup><col...
Hi all,
I was wondering if anyone knows if you can use IfAnd statement within a TExtjoin to set multiple criteria. I have been able to use If statements but i am unable to get a ifand Statement to work.
{=TEXTJOIN(CHAR(10),1,IF(AND(B4:B15=F16,H4:H14=D17),K4:K14,""))}
Hello,
I have several columns which contain numbers. I'd like to use TEXTJOIN with IF so that if, say column J includes the number 1 and column M also contains the number 1, then the column headers will be concatenated/joined (so the result would be "J, M".
I've made various unsuccessful...
Is it possible to nest SUBSTITUTE within an IF formula? I have a column that has a range of numbers in it that I want to combine in a TEXTJOIN string. However IF the number is a decimal, I need to substitute the decimal with an underscore. i.e. instead of 6.875 it needs to be 6_875. I'm not...
Hi,
There are formulas already to extract unique items from a range but all of them are based/depends on comparing the list column reference by COUNTIF.
Is there anyway to retrieve a unique array alone, for example may work in TEXTJOIN function?
E.g. TEXTJOIN(", ",0,UNIQUEFUNCTION)
Thanks a...
Hello,
I am trying to use TEXTOIN in vba
Sub AAA()
Dim str As String
str = WorksheetFunction.TextJoin(" ", False, "E2:E6")
MsgBox str
End Sub
This gives "E2:E6" as the return
If I try this instead:
str = WorksheetFunction.TextJoin(" ", False, E2:E6)
I get error
Is there a...
Is it possible to nest TEXTJOIN in a LOOKUP function? I want it to look up what's in B4, look for a match in an array (REF!$B$2:REF!$G$24) and TEXTJOIN what's in REF! columns F and G the same row as what it found in B4. So if the value in B4 is located at REF!B12 in the array, then TEXTJOIN...
Is it possible to add an IF function to TEXTJOIN? I would like to make it so if any number in column 5 in the first VLOOKUP is more than 95 then it gives the message "BP". Can this be done?
Here is the formula: =TEXTJOIN("x",,VLOOKUP(A3,FOLDED!$A$2:$I$7,5),VLOOKUP(A3,FOLDED!$A$2:$I$7,6))...
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.