Combining data in excel columns


In the example shown, the formula in E5 is:. J8 and "ids" is combining data in excel columns named range H5: This combining data in excel columns pulls the customer name and state from the customer table into the order table.

With the column number hard-coded as 2 customer names are in column 2 and the array set to the named range "data" H5: The formula to retrieve customer state is almost identical. The only difference is the column number is hard-coded as 3, since state info appears in the 3rd column:. J4, you can use a formula like this:.

Formulas are the key to getting things done in Excel. You'll also learn how to troubleshoot, trace errors, and fix problems. View the discussion thread. Skip to main content. In the example shown, the formula in E5 is: In the example shown, the formula in cell H6 is: In the example shown, the formula in J8 is: You can use index to retrieve individual values or entire rows and columns.

Excel Formula Training Formulas are the key to getting things done in Excel. Read this before posting a question. Thank you for the amazing work. Adding value to everyone's life Excel video training Quick, clean, and to the point.

During that session, Reza showed a cool trick to merge data based on two columns through the user interface… without concatenating the columns first. Elsewhere in the data the same account exists in multiple departments. Now, with both of those created, I want to merge the data so I get the account name on each row of the Transactions table. Originally I would have edited each query, selected the Acct and Dept columns, and merged the two columns together, probably separating them with a custom delimiter.

This can be done via the Merge command on the Transform or the Add Column tab. Essentially, by concatenating the columns, I end up with a single column that I can use to dictate the matches.

The data now looks like this, asking for us to select the column s we wish to use for the merge:. And Power Query indicates the order of the columns you selected. It will combining data in excel columns use this as a temporary concatenated value! This is pretty combining data in excel columns, although not super discoverable.

The really nice piece here is that it can save you the work of creating extra columns if you only need them to merge your data. How cool is that? Ken, I found this by neccessity a couple of weeks ago. I thnk it will work for more than two columns. Deserves to be much more discoverable. Hey Alex, it absolutely will. I've tested it with up to 3, but can't see why you wouldn't be able to use many more than that should the task require it. Hi Ken, It is a known feature.

The order of columns in tables for merging doesn't matter, but order of clicking on them is very important. Known, but not to me and others, of course. I'm still amazed by how much stuff there is to learn about this tool. Hello, Thanks for this blog. I am a recent user in Power Query and the this trick is amazing! I used to create tempary concatenated columns to use as a match before merge.

I noticed combining data in excel columns than when I merge a column, I lose some rows when I expand, even though the Estimating matches based on data preview have the same values. I am not the only user to have this issue, it was highlighted here, but without an adequate solution and I tried everything that was mentioned in the post.

Did you encounter a similar issue by any chance? I might try doing the concatenation manually to make sure it comes up the same, then doing the merge based on that instead of trying to do it in the merge. Maybe something is getting lost in there? I'm sure combining data in excel columns get back to you on it. Hi there, Thanks for your advice.

I notice that actually rows were not disappearing, it is the opposite, I have extra rows after expanding. It might be due to my merge table having duplicates, but I am not sure. The issue is that since the table is larger than the lines in the preview, I cannot combining data in excel columns at least not in one go the resulting table to compare it with my merge table. I can almost guarantee that you've got duplicates in the tale that you are trying to merge combining data in excel columns.

You can deal with that pretty quickly though Go to the merge table that you are merging in. I assume you've got a query that pulls that data in, so once you've done that, remove duplicates from the column you'll use to create your merge. Remove duplicates is on the right click menu. I've been concatenating combining data in excel columns until now. I knew this had to be possible Hi There, Thank you so much for this useful trick! This is day 3 for me using power query.

I was so excited when I found this website. I tried it on my data in power query for Excel After merging the two columns in both tables, I got a warning message saying" We were unable to determine combining data in excel columns many matches the selection will return".

I clicked ok anyways and I got this error message below DataFormat. We couldn't convert to Number. G I dont know what happened. Could it be because Table A has rows and combining data in excel columns columns while Table B has rows and 3 columns. Does the number of rows affect the merge? I did a left outer join from Table A, matching from Table B. Hi Christol, what's happening is that the data type in one of combining data in excel columns original queries is set to be numeric, and the other query is set to be text.

If you go back to the two individual queries, make the last step to convert the column you used for the join to text showing the ABC logo in the top left corner of combining data in excel columns column. That should fix it for you. I am able to merge both tables but i am combining data in excel columns getting the same error message. Also, the merged table wont let me select the only column I am interested in from the merged results.

I have to select all the columns to be able to view the merged table. I also think that the merged query left out some data because it has rows and 8 columns. But Table A has rows and 5 columns while Table B has rows and 3 columns. Could it be that the null values are affecting the output of the merge in both tables? I tried using power pivot Join by concatenating but it wont let me because the relational column in both tables doesn't have unique values.

Any suggestions on how I can approach this? The ABC means that it is an "any" data type, presumably because there is both text and numeric data in the preview. Remember also that you are combining data in excel columns working with previews in Power Query, so it may not load all the data at once. I just want to confirm, you have two separate queries, let's call them Table A and Table B. Because that should work. If it doesn't, can you maybe post in our forum at http: Your email address will not be published.

The Issue Assume for a second that we have data that looks like this: To get started, I created two connection only queries, one to each table. Right click the Transactions query in the Workbook Queries pane Choose Merge Select the COA query The data now looks like this, asking for us to select the column s we wish to use for the merge: So now do the same to the COA table: And then complete the merge.

As you can see, you get a new column of data in your query: End Thoughts This is pretty cool, although not super discoverable. I've modified the original post to refer to the right correct table. Hey Bill, Known, but not to me and others, of course. If you're still getting duplicates afterwards, then there is something else going on Looking forward to your feedback, thank you!

Looking forward to your feedback, thanks! Leave a Reply Cancel reply Your email address will not be published.

Last week, I was working with some data when I encountered the need to merge two staggered columns. That is to say, I needed to merge two columns where the data was blank in either one column or the other.

Then I place my cursor on the other column, right-click, then activate the Paste Special dialog box. As you can see, all the data from the Pulls column has combining data in excel columns copied to the Placements column without overwriting the data that was originally in Placements. If this is something repetitive that has to be repetitively, then perhaps a VB solution would be useful. Here is a macro that should execute fairly quickly…. I just looked back at my posting and am wondering how that second line of code got in there.

Here is the code without it although I did add code to turn off the screen updating at the beginning and back on at the end to speed things combining data in excel columns a little bit…. I had a question regarding finding text in a string. This is good but it only finds the full word. I would much appreciate your input and again my apologies to those following this thread.

Where did you combining data in excel columns my ExactWordInString function posted at? I would think you could have posted your question there. Combining data in excel columns contact me at rickDOTnewsATverizonDOTnet replace upper case letters with the symbols they spell and repeat your question in your email message so I will know who you are and why you wrote to me.

Your email address will not be published. Here is an example. I needed to bring placements and pulls into one column. I simply copy the data in one column — with all the blanks. Now, I can reformat the table so that only one column shows.

See that — no need for formulas or code. ExcelTips and Tricks. On Error Resume Next. Leave a Reply Cancel reply Your email address will not be published.