[Solved] Row order

Discuss the spreadsheet application
Post Reply
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

[Solved] Row order

Post by Stockman1 »

I have a number of suppliers providing a range of products of one particular brand. The products are identifiable from the barcode, which is present whoever supplies the goods. I have a price and a supplier code for each of the barcodes that they can supply.
I need to get them into price order (lowest first, highest last) and to take the supplier code with them to the adjacent cell as they move. This gives us a spreadsheet we can feed into our ordering system so that it orders from the cheapest first, if available, and then from the second cheapest if not and so on.
With two or three suppliers I can do it with If & And statements. But at four suppliers I have found the nesting makes the equation a bit cumbersome and long-winded. I have 8 suppliers in some cases and the nestings make the formula too long to use.
It's quite difficult to explain. Here is an image of what I envisage:
Forum.png
Any suggestions welcome thanks
BTW. There are 2,000+ lines which change daily or weekly hence the need to get something automatic.
Attachments
Help.ods
(11.72 KiB) Downloaded 66 times
Last edited by Hagar Delest on Thu Oct 18, 2018 12:04 am, edited 1 time in total.
Reason: tagged solved
Open Office 4.1 / Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Row order

Post by Zizi64 »

In my opinion your data in the sample file has a wrong structure. It is not possible to work efficiently with it. You need restrusture it to a "database like" format if you want use the Filter functions.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Row order

Post by Stockman1 »

OK. Thanks for looking at it.
Open Office 4.1 / Windows Vista
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Row order

Post by robleyd »

The data you get is possibly in a more structured format; perhaps if you supply a sample of that information, including part numbers, prices, barcodes etc from several suppliers of the same items, someone may be able to make some suggestions.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Row order

Post by Stockman1 »

Yes, I get 3 columns from each company in csv files. Barcodes, Part number and Price.
I've already done the VLOOKUP bit to enable the comparison of prices on like-for-like items.
I've recreated it in the above example because it is commercially sensitive information that I am sent.
I figured that whatever method somebody found a Lookup would need to be done as some suppliers sell <250 of the 2000+ products, others sell 1800+.
I could make an example of what I am sent, but surely the lookup stage would come first and just give the stage that I am at already.?
Open Office 4.1 / Windows Vista
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Row order (SOLVED)

Post by Stockman1 »

OK. It's sorted now.
Just in case somebody else has a similar situation in the future, I did it like this:
I used the MIN function for each row to get the lowest value on the row.
Then I used the IF function next to that newly generated cell to drag over the corresponding code.
I now have two new columns containing the lowest price for that row and the corresponding code.
Then I copied over the whole table using IF again to make sure that the the whole table was copied over except the values that had just been found in the new columns.
Then I repeated the process, finding the next lowest value. And so on.
Eventually I had an empty table and 8 x 2 columns which, when placed side by side, show all the items in price order with the matching code.
Not very elegant I know. I don't have to look at it though. I just use a macro to feed the raw prices and codes into it and it generates a file just how I want it.
Solved
Open Office 4.1 / Windows Vista
Post Reply