Ask Your Question
0

Aligning two lists in Calc problem fixed by Gilberto

asked 2019-05-10 09:12:20 +0200

stuarts.burgers gravatar image

updated 2019-05-11 05:48:21 +0200

Hi, I have two lists from different sources that I need to make sure that the contents of the lists are positioned in the same place within their cell. C:\fakepath\street names.ods

In the above example, I need the contents "Green Cells" to be positioned in exactly the same position within their cell as the contents of the "Yellow Cells".

I have attempted to add or subtract spaces but to no avail.

What I am attempting to do is use Matching formula "=MATCH(E2&D2,$'Mailman''s Route'.A$1:A$538&$'Mailman''s Route'.B$1:B$538,0)" where the "Green Cells" are E and the "Yellow Cells" are $'Mailman's Route' .A$1:A$538.

The other part of formula works, if I cut and paste from "E" into "A$" and I can get a Match to work without doing anything to the "D" and "B$" cells.

The Yellow List is taken from a database and arrives to me an Excel Sheet that I open in Libre Calc

As I have 16 Green List and up to 33 Yellow Lists per Green Lists I really need to automate.

I have done the cut and past thing as a workaround but I would like to automate the process as I do not have the time to do cutting and pasting.

Thanks Stuart

edit retag flag offensive close merge delete

Comments

I don't understand at all but in your example the strings are different due to one or more "space" characters at the end of the strings in the "Yellow Cells" and thus they do not align to the same posiition like the same word (not having these extra space characters) in the "Green Cells". So is it really just about to get rid of the extra spaces at the end of the strings? If yes - then imo the summary of this question is completely misleading.

Opaque gravatar imageOpaque ( 2019-05-10 15:28:01 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-10 15:34:59 +0200

updated 2019-05-10 15:39:35 +0200

Select the entire area (A1: E21)

Edit / Find & Replace

in Find "type two spaces"

in Replace "leave blank"

click [Replace All]


ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

Comments

Giberto Thank you it works brilliantly, thank you for your input and wisdom, you have just saved me a huge amount of time. I knew the problem was with the spacing but I could no see how to fix it

stuarts.burgers gravatar imagestuarts.burgers ( 2019-05-11 05:43:23 +0200 )edit

Ok @stuarts.burgers, I'm glad to have helped, if you have more questions, just post here. we're on duty.

Ok @stuarts.burgers , fico contente em ter ajudado, se tiver mais dúvidas, é só postar aqui. estamos de plantão.

Gilberto Schiavinatto gravatar imageGilberto Schiavinatto ( 2019-05-11 13:46:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-10 09:12:20 +0200

Seen: 41 times

Last updated: May 11