CODEWITHSUNDEEP
excel
Kelcher
Kelcher

Reputation: 5

How to copy a data row from column A to column B, between each data row

Troubles with the update formula Troubles with formula, asking for a missing matrix Steps, I have tried to retrieve data from column B to column D Know is telling that I insert insufficient argumentsGood afternoon,

I have column B, with descriptions in Portuguese, row by row and column D with the translations in English: I'm trying to insert in column D the corresponding translation in Portuguese under each data row in English.

But I can't find any formula to do that, also I didn't find any question like this in the forum. The only nearest question about, is to insert a blank row between data rows with this formula =MOD(ROW(D2),2)=0 or with a filter adding series. And retrieving data with vlookup, as in the attached image.

Upvotes: 0

Views: 356

Answers (2)

Terry W
Terry W

Reputation: 3257

You can use power query to tackle this task.

I have used the following data for demonstration. Please note I am using Excel 365 English version.

| Portuguese | English |
|------------|---------|
| um         | one     |
| dois       | two     |
| três       | three   |
| quatro     | four    |
| cinco      | five    |
| seis       | six     |
| Sete       | seven   |
| oito       | eight   |
| nove       | nine    |
| dez        | ten     |

Steps are:

  1. Load/Add the data set to Power Query Editor;
  2. Make a duplicated column of Portuguese, then add an Index column with index starting from 1, then you should have something like below:

Added Duplicate and Index Column

  1. Use Merge Columns function under Transform tab to merge the English column with the Portuguese - Copy column with a custom delimiter such as hashtag # (as long as this delimiter is not part of your original texts), then you should have:

Merged

  1. Use Split Columns function under Transform tab to split the merged column by the same delimiter #, and make sure in the Advanced Settings to choose to put the results into Rows as shown below:

Splited

The output will look like the following:

Output

You can choose to remove the Portuguese column if you do not want to show it in the final output, then Close & Load the table to a new worksheet (by default).

Here are the power query M Codes behind the scene. All functions used are within GUI so should be easy to follow and execute.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portuguese", type text}, {"English", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Portuguese", "Portuguese - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 1),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"English", "Portuguese - Copy"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}})
in
    #"Changed Type1"

Let me know if you have any questions. Cheers :)

Upvotes: 0

user783388
user783388

Reputation:

Put this formula in the Result Column and adjust $A$1 to your first portuguese term and $B$1 to you first translated term:

'=OFFSET($A$1;((ROW()-ROW($B$1))/2)-ROW($A$1)+1;0)

You should get a Column, where every Portuguese term is repeated. Now you can overwrite the formulas in the upper cells with the english translations.

The formula calculates the difference between current (translated row) cell and the first translated row and cuts it in half: that is the row of the portuguese term to associate with this cell. Then it uses that row number as offset to the first row of portuguese terms.

Now, if you want to have the forst row empty, you can of course fill the whole formula in the true part of an if formula:

=IF(MOD(ROW()-ROW($B$1),2)=0;"";OFFSET($A$1;((ROW()-ROW($B$1))/2)-ROW($A$1)+1;0))

That is something you will often do in excel and I assume you know that trick. It makes the core formula a little harder to read, but it basically says: if the current row inside this block is divisible by two, then set the row empty, else set the row equal to the formula I presented above.

Upvotes: 0

Related Questions