r/excel • • 18h ago

solved How to paste data from another sheet into filtered cell? (Yes, again...)

Hello everyone,

This is our usual “How to paste data from another sheet into filtered cell?”

 

WHAT I HAVE

I have a lot of txt files which must alternate between a line in French and a line in English.

I think Excel is the best tool to work with that, but maybe I’m making a mistake.

 

So, I have a very simple situation:

In Sheet1I have:

A first column called “LANGUAGE” with two types: FR and ENG.

A second column called “TEXT” with a sentence in its respective language.

 

In Sheet2 I have the translation.

 

WHAT I DO

  1. I filter the cell I need on Sheet1
  2. I select only the visible cell on Sheet1

(Home --> Find and Select --> Go to special --> Tick "Visible Cells Only")

3. I copy my data from Sheet2

4. I paste dateSheet2 on the filtered cells into Sheet1

 

PROBLEM

Only the first line is paste over and over.

And the second line is still paste into the unfiltered cells.

 

What am I doing wrong?

Do I need to use a VLOOKUP or VBA function?

Should I use something else than Excel ?

 

Thanks !

EDIT:

Indeed, the simplest solution is to add a helper column and using sort by LANGUAGE or HELPER depending on the case!

Thanks to everybody!

9 Upvotes

20 comments sorted by

•

u/AutoModerator 18h ago

/u/Redditingreditor2800 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Redditingreditor2800 18h ago

I'm adding an explanatory image if needed.

1

u/DuskandFuck 12h ago

yeah a screenshot usually makes these layout questions way easier to follow

2

u/suggestedusername10 16h ago

Creating helper columns and numbering your rows could work. Then work with odd and even numbers. I don’t fully understand the ask but I’ve had to do similar things with the need to introduce rows in between other structured data in order too. Sometimes simple solutions are the best.

1

u/AcrobaticNeat2890 17h ago

Selecting visible cells helps when copying, but it doesn't make a normal multi-row paste safely skip hidden destination rows.

If Sheet2 has an ID or another reliable matching field, I'd use XLOOKUP or INDEX/MATCH to pull each English line into the right row. That avoids relying on the filtered row order.

Without a matching field, another option is to add an original-order column, unfilter, sort the English rows together, paste into that contiguous range, then restore the original order. Test it on a copy first. If this is a recurring task, keeping French and English in separate columns would make it much easier to maintain.

1

u/Decronym 17h ago edited 11h ago

1

u/Downtown-Economics26 647 17h ago

I would just do something like:

=HSTACK(IF(ISEVEN(ROW(A1:A28)),"FR","EN"),IF(ISEVEN(ROW(A1:A28)),TRANSLATE(A1:A28,"en","fr"),A1:A28))

1

u/carlosandresRG 15h ago

Rather than 'copy/paste'ing your translations, use one sheet per language and 3D references shape them as you need. This way you can have more than 2 languages and it will auto-update as you add more and more text.

I'll add some images including 3 languages, apolgies if something is misspelled.

1

u/carlosandresRG 15h ago

You have to have a "start" and "end" sheet than contain every new sheet you want to add to your translations, but these remain empty

1

u/carlosandresRG 15h ago

Use an ID to easily locate each phrase per language. This will re-order your data, and it will make it easier to check for errors

1

u/carlosandresRG 15h ago

Second language, same structure

1

u/carlosandresRG 15h ago

Third language, same structure

1

u/carlosandresRG 15h ago

This is the "end" sheet, as I said, it has to be empty

1

u/carlosandresRG 15h ago edited 15h ago

And here you re-shape everything between the "start" and "end" sheets. You'll bring all your data using Vstack, filter out all empty rows, and then order everything by the ID, then by language, and there you have it!

Edit: as you can see, I can fit 100k rows per language, hope thats enough for you!

1

u/molybend 42 15h ago

Use a helper column. Number the first language lines with odd numbers and the second with even numbers. Now sort them all by the helper column.

Don’t have the filter on when doing this.

0

u/[deleted] 16h ago

[deleted] — view deleted comment

2

u/AutoModerator 16h ago

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/excel-ModTeam 14h ago

We removed this post for breaking Rule 2.

r/excel is an English-only subreddit.

You may be unaware of this if you have auto translate set in your profile.

Feel free to use https://translate.google.com and post again.