[Solved] Find same line of numbers

Discuss the spreadsheet application

[Solved] Find same line of numbers

Postby Soricelus » Mon Jan 06, 2020 8:26 pm

Hello guys,
I've tried in the last 2 days to figure out how to find similar lines in my table and I can't understand how to do it.
I've looked on multiple threads and read a lot about it but still... can't figure out hence why I'm looking to see if any of you can help me with a solution.
There are 5 columns (for 5 vans) and deliveries done across 3 years (4th one starting now) so there will be about 1000 lines.
Every day we need to do deliveries to 6 shops. In some days 1 van can cover even 4 shops, other days just 1. It depends when and where I need them.
Apart from many other data I am collecting from 3 complete years of deliveries, I need to find out if there are any exact matches of daily working vans.
Basically if the lines are looking like bellow:
0 1 2 1 0
2 0 0 2 2
1 1 1 2 1
2 0 0 2 2

I need to find the lines that are similar (not just as numbers but as position to) -> 2 0 0 2 2
All I could find till now was how to search in every column but nothing to return me an entire line that is similar with other line/lines.
Not sure if it help but since there is not a huge number of options, I would be able to search individually all the options (I'm thinking that the search might return more than 1 similar type of lines).

I have attached a file to this topic. I have 50 lines completed with data.
Is there any possibility to do it ?
Thank you in advance,
Sorin
Attachments
Cinnabon Deliveries Statistics.ods
(11.79 KiB) Downloaded 19 times
Last edited by Soricelus on Mon Jan 06, 2020 10:10 pm, edited 1 time in total.
Windows 10 Apache OpenOffice 4.1.6
Soricelus
 
Posts: 2
Joined: Mon Jan 06, 2020 7:46 pm

Re: Find same line of numbers

Postby FJCC » Mon Jan 06, 2020 9:51 pm

If I understood you correctly, here is one solutions. I combined all of the values from each row into a code number and then used COUNTIF() to find how many times each code number appears.
Attachments
Vans.ods
(17.46 KiB) Downloaded 23 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find same line of numbers

Postby Soricelus » Mon Jan 06, 2020 10:02 pm

Bloody brilliant!
I'm amazed how quick you've done it. I've looked at filters solution but lost myself further.

I thank you very much for it!
Problem solved
Windows 10 Apache OpenOffice 4.1.6
Soricelus
 
Posts: 2
Joined: Mon Jan 06, 2020 7:46 pm

Re: Find same line of numbers

Postby Villeroy » Mon Jan 06, 2020 10:28 pm

This method fails because there are gaps in the list.
"1221" may mean any of:
<blank>1221
1<blank>221
12<blank>21
122<blank>1
1221<blank>

I simply filled up the date column and created a pivot table. You may even aggregate by year and month.
Attachments
Cinnabon Deliveries Statistics_Pivot.ods
(20.35 KiB) Downloaded 22 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 21 guests