SOLVED

Filter with line merged

%3CLINGO-SUB%20id%3D%22lingo-sub-1552828%22%20slang%3D%22fr-FR%22%3EFilter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552828%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnder%20Excel%2C%20I%20would%20like%20to%20filter%20a%20table%20where%20some%20lines%20are%20merged.%3C%2FP%3E%3CP%3EThe%20lines%20are%20merged%20by%20two%20lines%20for%20the%20first%20three%20columns.%3C%2FP%3E%3CP%3EWhen%20I%20have%20filter%20one%20of%20the%20first%20three%20columns%2C%20Excel%20show%20only%20the%20first%20line%20of%20the%20merged%20line%2C%20why%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1552828%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1553014%22%20slang%3D%22de-DE%22%3ESubject%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1553014%22%20slang%3D%22de-DE%22%3EA%20pre-prepared%20template%20would%20be%20an%20advantage%20%3CBR%20%2F%3E%20...%20but%20try%20this%20formula%20%3CBR%20%2F%3E%20%3DA2%26amp%3B%22%2C%22%26amp%3BB2%26amp%3B%22%2C%22%26amp%3BC3%26amp%3B%22%2C%22%26amp%3BD4%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554310%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743503%22%20target%3D%22_blank%22%3E%40LE_TOULLEC%3C%2FA%3E%26nbsp%3B%2C%20this%20is%20normal%20Excel%20behavior.%20If%20cells%20A2%20and%20A3%20are%20merged%2C%20only%20cell%20A2%20will%20have%20the%20value.%20You%20can%20test%20this%20by%20typing%20%3DA2%20in%20B3%20and%20%3DA3%20in%20B3.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIMHO%2C%20merged%20cells%20are%20nothing%20but%20trouble.%20I'd%20recommend%20removing%20them%20and%20repeating%20the%20items.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554922%22%20slang%3D%22de-DE%22%3ESubject%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554922%22%20slang%3D%22de-DE%22%3EA%20demo%20file%20would%20help%20you%20solve%20the%20problem%20faster.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555105%22%20slang%3D%22fr-FR%22%3EBetreff%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555105%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20find%20in%20attachment%20file%20an%20example.%3C%2FP%3E%3CP%3EWhen%20I%20filter%20by%20name%2C%20I%20would%20like%20to%20show%20two%20lines.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555149%22%20slang%3D%22de-DE%22%3ESubject%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555149%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743503%22%20target%3D%22_blank%22%3E%40LE_TOULLEC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EVoici%20quelques%20petits%20exemples%20et%20un%20lien%20de%20Microsoft.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%20Si%20cela%20correspond%20vos%20id%C3%A9es%20en%20tant%20que%20solution%2C%20veuillez%20la%20marquer%20comme%20la%20bonne%20r%C3%A9ponse%2C%20afin%20que%20les%20autres%20inform%C3%A9s%20puissent%20%C3%A9tre%20inform%C3%A9s%20...%20mieux%20avec%20un%20like.%3C%2FSPAN%3E%20%3CSPAN%3ESi%20vous%20n'avez%20pas%20aim%C3%A9%20la%20solution%20propos%C3%A9e%2C%20veuillez%20donner%20un%20bref%20commentaire.%3C%2FSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-499686674%22%20id%3D%22toc-hId-499686674%22%20id%3D%22toc-hId-499686674%22%20id%3D%22toc-hId-499686674%22%20id%3D%22toc-hId-499686674%22%3E%3CFONT%20size%3D%224%22%3ECombiner%20le%20texte%20de%20deux%20cellules%20ou%20plus%20en%20une%20cellule%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Ffr-fr%2Foffice%2Fcombiner-le-texte-de-deux-cellules-ou-plus-en-une-cellule-81ba0946-ce78-42ed-b3c3-21340eb164a6%3Fui%3Dfr-fr%26amp%3Brs%3Dfr-fr%26amp%3Bad%3Dfr%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Ffr-fr%2Foffice%2Fcombiner-le-texte-de-deux-cellules-ou-plus-en-une-cellule-81ba0946-ce78-42ed-b3c3-21340eb164a6%3Fui%3Dfr-fr%26amp%3Brs%3Dfr-fr%26amp%3Bad%3Dfr%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EPs.%20Les%20cellules%20fusionn%C3%A9es%20ne%20peuvent%20pas%20%C3%A9tre%20trait%C3%A9es%20comme%20des%20cellules%20uniques.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EJe%20sais%20que%20je%20ne%20sais%20rien%20(Socrate)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId--1307767789%22%20id%3D%22toc-hId--1307767789%22%20id%3D%22toc-hId--1307767789%22%20id%3D%22toc-hId--1307767789%22%20id%3D%22toc-hId--1307767789%22%3E%26nbsp%3B%3C%2FH1%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555288%22%20slang%3D%22en-US%22%3EBetreff%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743503%22%20target%3D%22_blank%22%3E%40LE_TOULLEC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20technique%20could%20be%3C%2FP%3E%0A%3CP%3E-%20copy%20%2F%20paste%20column%20you'd%20like%20to%20filter%20into%20another%20one%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20568px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208970i501B007D539E0F33%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20unmerge%20Name%20column%2C%20on%20ribbon%20Home%20-%26gt%3B%20Find%20and%20Select%20-%26gt%3B%20Go%20to%20Special%20-%26gt%3B%20select%20Blanks%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20443px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208973i527325C008FAE7CB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20type%20%3DB4%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20255px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208974iDB9B2812B7909A2A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20%3CSTRONG%3ECtrl%2BEnter%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208975iD3CECFAAB354B61C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20select%20column%20where%20you%20copy%20Names%2C%20Format%20Painter%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20421px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208976i212657F5EA69341D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20apply%20it%20to%20Name%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20221px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208977iD43A613183A7590B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20you%20may%20filter%20on%20Name%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20456px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208978i73B0843B12F8AB7A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555465%22%20slang%3D%22fr-FR%22%3EBetreff%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555465%22%20slang%3D%22fr-FR%22%3EWonderFull%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555911%22%20slang%3D%22en-US%22%3EBetreff%3A%20Filter%20with%20line%20merged%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743503%22%20target%3D%22_blank%22%3E%40LE_TOULLEC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGlad%20to%20help.%20I%20believe%20such%20approach%20is%20described%20in%20few%20posts%2C%20you%20may%20google%20for%20them%20to%20check%20more%20step-by-step%20details%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

Under Excel, I would like to filter a table where some lines are merged.

The lines are merged by two lines for the first three columns.

When I have filter one of the first three columns, Excel show only the first line of the merged line, why ?

8 Replies
Highlighted
A pre-prepared template would be an advantage
... however try this formula
=A2&","&B2&","&C3&","&D4

Nikolino
I know I don't know anything (Socrates)

Highlighted

@LE_TOULLEC , this is normal Excel behavior. If cells A2 and A3 are merged, only cell A2 will have the value. You can test this by typing =A2 in B3 and =A3 in B3. 

 

IMHO, merged cells are nothing but trouble. I'd recommend removing them and repeating the items.

Highlighted
A demo file would help you solve the problem faster.
Highlighted

Hello@Nikolino 

You can find in attachment file a example.

When I filter by name, I would like to show two lines.

Highlighted

@LE_TOULLEC 

Voici quelques petits exemples et un lien de Microsoft.
Si cela correspond à vos idées en tant que solution, veuillez la marquer comme la bonne réponse, afin que les autres informés puissent être informés ... mieux avec un like. Si vous n’avez pas aimé la solution proposée, veuillez donner un bref commentaire.

Combiner le texte de deux cellules ou plus en une cellule

https://support.microsoft.com/fr-fr/office/combiner-le-texte-de-deux-cellules-ou-plus-en-une-cellule...

 

Ps. Les cellules fusionnées ne peuvent pas être traitées comme des cellules uniques.

 

Nikolino
Je sais que je ne sais rien (Socrate)

 

 

Highlighted
Best Response confirmed by LE_TOULLEC (New Contributor)
Solution

@LE_TOULLEC 

The technique could be

- copy / paste column you'd like to filter into another one

image.png

- unmerge Name column, on ribbon Home -> Find and Select -> Go to Special -> select Blanks

image.png

- type =B4

image.png

and Ctrl+Enter

image.png

- select column where you copy Names, Format Painter

image.png

and apply it to Name column

image.png

Now you may filter on Name

image.png

Highlighted
WonderFull
Highlighted

@LE_TOULLEC 

Glad to help. I believe such approach is described in few posts, you may google for them to check more step-by-step details