Hi Everyone, I'm back with a question about the multi row formula tool.
So, I have data that looks like this that I've used a find replace to set up and see if its even possible to do.
Account | Account desc | X | Y | Z | Acct2 | Account desc2 | x2 | y2 | z2 |
32KS64012 | ABC | IS | MKTG | 2700 | null | null | null | null | null |
32KS64209 | DEF | IS | MKTG | 7200 | 32KS6420 | DEG | IS | MKTG | 4500 |
32KS64012 | GHI | IS | MKTG | 1200 | null | null | null | null | null |
The information starting at Acct2 and onward is appended based on similarity because they are new accounts. I want to keep the ordering of my data on the left but add the new information in rows below where the Account is similar. So the new information would look like this essentially.
Account | Account desc | X | Y | Z | Acct2 | Account desc2 | x2 | y2 | z2 |
32KS64012 | ABC | IS | MKTG | 2700 | null | null | null | null | null |
32KS64209 | DEF | IS | MKTG | 7200 | 32KS6420 | DEG | IS | MKTG | 4500 |
32KS6420 | DEG | IS | MKTG | 4500 | null | null | null | null | null |
32KS64012 | GHI | IS | MKTG | 1200 | null | null | null | null | null |
What is left over on the last fields from acct2 and on doesn't matter to me as I won't need them after adding it to the row below.
I can provide you examples of the data sets before appending them if you think using something other than a multi row formula tool would be better suited.
Thank you in advance!!
Solved! Go to Solution.
Hi @WestonProhaska ,
An example of the data before appending would be useful if you can provide it.
Also, how do you characterise the "similarities" ? On a particular field ?
Kind regards,
Jean-Baptiste
Hi @WestonProhaska ,
Attached is an example showing how to get the result you want from the appended data you shared.
I'm not sure it is the easiest way, but it does the job.
Let me know if that works for you.
Best,
Fernando Vizcaino
@Jean-Balteryx
This is Data set one thats in the order I want to keep
Account | Account desc | X | Y | Z |
32KS64012005 | ABC | IS | MKTG | 2700 |
32KS64209003 | DEF | IS | MKTG | 7200 |
32KS64012001 | GHI | IS | MKTG | 1200 |
This is the data I've appended.
I basically want every new record appended into a position relative to the old. I appended based on similar beginnings in Account.
Account | Account desc | X | Y | Z | new or old |
32KS64209017 | ABC | IS | MKTG | 2700 | new |
32KS64209100 | DEF | IS | MKTG | 7200 | new |
32KS64209101 | GHI | IS | MKTG | 1200 | new |
I attached a workflow doing what I think may solve your problem.
Basically it unions your data in a single table then sort on the account description alphabetically.
Don't hesitate if you have questions or want more details.
Kind regards,
Jean-Baptiste
First, thank you both for taking the time, but it doesn't quite achieve what I want.
I'll clarify.
My data currently looks like this.
Set 1
Acct | Acct Desc | bs/is | level | balance |
800 | x | x | x | x |
601 | x | x | x | x |
700 | x | x | x | x |
603 | x | x | x | x |
500 | x | x | x | x |
Set 2
Acct | Acct Desc | bs/is | level | balance | new or old |
501 | x | x | x | x | new |
602 | x | x | x | x | new |
701 | x | x | x | x | new |
604 | x | x | x | x | new |
502 | x | x | x | x | new |
My desired end data
Acct | Acct Desc | bs/is | level | balance | new or old |
800 | x | x | x | x | |
601 | x | x | x | x | |
602 | x | x | x | x | new |
700 | x | x | x | x | |
701 | x | x | x | x | new |
603 | x | x | x | x | |
604 | x | x | x | x | new |
500 | x | x | x | x | |
501 | x | x | x | x | new |
502 | x | x | x | new |
I should have layed this out clearer. I absolutely have to keep the ordering from the original data and just add the new accts based upon that ordering. Sorting will mess it up.
Thanks again for helping out guys. Hopefully we can find a solution
Hi @WestonProhaska ,
Can you share some real accounts? Because it's possible to develop something that simplifies your problem.
Do the accounts have the same number os characters or could be more or less?
Best,
Fernando Vizcaino
Here's some from real account numbers where order needs to stay the same
32KS64209014 |
32KS64209016 |
32KS64002003 |
32KS64001011 |
32KS64011012 |
32KS64209002 |
32KS64001002 |
32KS64209012 |
Here's some of the new ones that need to be added where the length differs
32KS6420 |
32KS64209017 |
32KS64209100 |
32KS64209101 |
32KS64209102 |
Sorry, would you be able to explain a little more?
It would be nice if you could show in different color new accounts being inserted, as below, but also varying the string size
Thank you!
The end data would look like this. From the originals above.
32KS6420 |
32KS64209014 |
32KS64209016 |
32KS64209017 |
32KS64209100 |
32KS64209101 |
32KS64002003 |
32KS64001011 |
32KS64011012 |
32KS64209002 |
32KS64001002 |
32KS64209012 |
71008348 |
72008500 |
71008450 |
70008210 |
53007000 |
53007200 |
52007000 |
52007200 |
52207000 |
52207200 |
90009050 |
90009010 |
54007000 |
54007200 |
71008365 |
Could also look like this as my end goal
71008348 |
71008365 |
72008500 |
71008450 |
70008210 |
54007000 |
54007200 |
53007000 |
53007200 |
52007000 |
52007200 |
52207000 |
52207200 |
90009050 |
90009010 |