Forum Discussion
Solved
How do I merge 2 columns
Ok so basically its a bit more complicated than that, I want to merge a phone number and home phone number into one column , but I want the home phone number to go to the next line in the merged column which also needs a foreign key to be repeated if the employee has 2 numbers entered.
Edit: I Managed to do it with a little help
None of your examples of LINEST is correct. See below for details.
-----
First, it is always bests to attach an Excel file that demonstrates the problem. That is out of respect for those who want to help; do not force them to re-enter your data and formulas. And it might result in a more accurate response; we might not be able to reproduce your results because the posted data is rounded.
"If a picture is worth a 1000 words, an Excel file is worth 1000 pictures". (wink)
But when you post images, always include row numbers as well as column names. I had to infer the row numbers from the cell references in your text.
-----
Second, it is always wise to copy formulas from the Formula Bar and paste them into your posting, instead of retyping them.
I was unable to reproduce your results because you posted LINEST formulas of the form =LINEST(U21:U36, V21:V36...).
But then I looked at "x" and "y" in U20 and V20, and I guessed that the actual formulas are of the form =LINEST(V21:V36, U21:U36....). That is, switching the U and V references. Now my results for "a", "b" and "c" appear to be similar to yours, to wit (see the attached Excel file for details):
-----
Finally, none of your examples of LINEST is correct.
The correct usage is demonstrated in AB21:AD25. With that range selected, enter the formula =LINEST(V21:V36, U21:U36^{1,2}, 1, TRUE)
Note: The formula must be array-entered (press ctrl+shift+Enter instead of just Enter) in versions of Excel before Excel 365 and Excel 2016.
Then the coefficients "a", "b" and "c" are in AB21:AD21. And r^2 is in AB23.
RSQ can also be calculated with the formula =RSQ(V21:V36, W21:W36) in W38. W21:W36 ("est y") is based on formulas of the form (W21) =SERIESSUM(U21, 2, -1, $AB$21:$AD$21)
Aside.... When displaying trendline formulas in charts, it is prudent to increase the number of decimal places by clicking the trendline, then Format Trendline Label. For example, note that the coefficient "a" is more accurately -6.94843E-08, not -7E-08.
-----
PS.... Although I recommend the array-entered LINEST formula above -- it is more efficient -- you can calculate the individual coefficients and r^2 with the following non-array formulas:
a: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 1)
b: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 2)
c: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 3)
r^2: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 3, 1)
7 Replies
Deerg65 You don't specify what "a foreign key" entails. But perhaps you mean that "04" should be in the beginning of every phone number. If so, try this in D2, set the cell format to wrap text and copy it down:
=TEXT(B2,"0000000000")&CHAR(10)&IF(ISBLANK(C2),"",TEXT(LEFT(B2,1),"00"))&C2
Run the following macro:
Sub MergePhone() Dim rng As Range Application.ScreenUpdating = False Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious) Do rng.Offset(1).EntireRow.Insert rng.Offset(1, -2).Value = rng.Offset(0, -2).Value rng.Offset(1, -1).Value = "'" & rng.Value Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious) If rng.Row = 1 Then Exit Do Loop Range("C:C").ClearContents Application.ScreenUpdating = True End Sub- Hey Hans, If I want to merge 6 columns instead of just 2 what would i need to change??
- Thank You Soo Much!! This is exactly what I needed!!
I didn't understand the ID#
You can use the following formula and then click on "Wrap Text" option:
=TEXTJOIN(CHAR(10),,B2:C2)
