vlookup adding data to a field with existing data?

Barbara Witt

If my spreadsheet has a notes column and notes are in the field. Then I use a form to collect information and notes via another spreadsheet. Once I pull the information from the second spreadsheet over to the first one, will the notes from the second spreadsheet be added to the notes on the first spreadsheet or will they be overwritten? Obviously I use a vlookup formula to match up my companies from the second spreadsheet to the first spreadsheet, so the new information is added according to each company. However, what about adding new notes to an already existing notes field or do I have to provide for two note fields?

Thanks for your assistance.

Andrée Starå

@Barbara Witt

You're more than welcome!

Yes, the primary note would be in the helper column.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Barbara Witt

@KDM Yes, that worked after I moved the second ) to the back. And now the delimiter shows up, too.

Here is a screen shot. The comma is after the room number.

=IF([Project Number]@row <> "", JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

Perfect. Thanks so much - as always.

Best regards, Barbara

Andrée Starå

Hi @Barbara Witt

If you add a formula to the notes field, it will overwrite the note, so if you want to keep them both, you'll have to add a so-called helper column.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Barbara Witt

Thanks, Andree. A "helper column" is a second notes column, right? Is that what you mean?

Andrée Starå

@Barbara Witt

You're more than welcome!

Yes, the primary note would be in the helper column.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Barbara Witt

I have notes from the Project Manager and several other folks. I was hoping to have one combined notes field, regardless who wrote the note. Is there a way to combine all notes into one field?

Andrée Starå

@Barbara Witt

Yes, there is. You can use JOIN or COLLECT. The best option depends on your specific structure.

Barbara Witt

Do you mean I can JOIN or COLLECT the notes from two different spreadsheets in my main (the third) spreadsheet? In total I would be combining notes from two spreadsheets into a new spreadsheet.

What if I already have notes in the main spreadsheet and want to add the notes from the other two spreadsheets to it? Thus I am keeping the notes in my first spreadsheet and then adding the notes from two other spreadsheets.

Would the latter work, too?

Barbara Witt

Thank you, Andree. I am still a little lost, however. Could you spell the formula out for me to combined notes from two fields into a third field?

Barbara Witt

Hi Andree, I still have some question on the JOIN/COLLECT formula.

I see that I join the words from two fields and I can separate them out with a "&" in between (or whatever I choose.

Is there a way to even have a generic intro in front of the first text, which is pulled into my field?

Essentially I would like to label where the notes came from. I can easily label the second note by using "& from Tech Services". I was hoping I can insert generic text in front of the first data pull, too.

Let me know your thoughts. Thanks Barbara

Barbara Witt

Hi @Andrée Starå , I still have some question on the JOIN/COLLECT formula.

I see that I join the words from two fields and I can separate them out with a "&" in between (or whatever I choose.

Is there a way to even have a generic intro in front of the first text, which is pulled into my field?

Essentially I would like to label where the notes came from. I can easily label the second note by using "& from Ambient Tech Services". I was hoping I can insert generic text in front of the first data pull (from iOS Teach Services:", too.

Let me know your thoughts. Thanks Barbara

Barbara Witt

Hi @Andrée Starå@KDM, I had not heard back from Andree, so I thought I'll try you. I still have some question on the JOIN/COLLECT formula.

I see that I join the words from two fields and I can separate them out with a "&" in between (or whatever I choose.

Is there a way to even have a generic intro in front of the first text, which is pulled into my field?

Essentially I would like to label where the notes came from. I can easily label the second note by using "& from Ambient Tech Services". I was hoping I can insert generic text in front of the first data pull (from iOS Teach Services:", too.

Let me know your thoughts. Thanks Barbara

Kelly Moore

Hey Barbara

I haven't tested this as I have a meeting coming up but try the text you want to insert, in quotes, with a + sign ahead of your join/collect function. If we were joining two text cells together, we would just write it as "cell1" + "cell2" . I'm thinking if you mimic that, it will work.

oh, and if you need spaces, delimiters in between, insert them within additional quotes, separated with + signs, mimicking this

"cell1" + " , " + "cell2"

edited: oops, in my haste I put quotes around cell1 and cell2. We wouldn't have to do that - only spaces or delimiters in between the + signs would need that

Let me know.

Kelly

Barbara Witt

Hi @KDM , I like your idea, it placed the comma in front of the first location, but not in-between each location....

=IF([Project Number]@row > 1, "," + JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row)))

Luckily it does keep the data in rows, if I hover over the cell (see screen shot below) I just want to be super save and add a comma, too.

Would you know of another way?

You are always so helpful. Thank you very much.

Best regards, Barbara

Kelly Moore

Oh, sorry, I misunderstood. I thought you were looking for a way to insert a prefix in the first one only.

Did you add a delimiter to the JOIN statement? I don't see it in your formula.

Try this

=IF([Project Number]@row > 1, JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row)), " , ")

https://help.smartsheet.com/function/join

Kelly

Barbara Witt

Interesting, @KDM

This put a comma in the location field, if there was no project number to compare (because the project number field is still empty). But it did not change anything if the location field had data in it.

That's strange, right?

Barbara

Kelly Moore

May I see a screenshot of that? I also had spaces before and after the comma. I'm wondering if your text wrap is breaking where the comma appears? Also, if you insert your cursor immediate after the comma that follows the double-parentheses - does the helper text indicate we're at the delimiter? The delimiter word would be highlighted yellow.