I am trying to come up with a formula that will search an entire row of data (row 2) for instances of the text "test" and if it finds an instance, return the cell to the left of it, then to the right of it, then two cells to the right of it.
There will be multiple instances of test in the same row so I want to find all of them.
Then I want to repeat that for every row in my spreadsheet.
Any ideas?
Hi,
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.
Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Here is an example. I would prefer that this is in a different sheet rather than on the front sheet itself (maybe a new sheet called Results) but if that is too hard it can be on the same sheet.
Once I do this farmer I will need results from every other farmer (for example the next one is cobblestonecreedfarm) so if there is an easy way to replicate this or create a sheet per farmer, that would be great too.
But once I have a basic formula I should be able to replicate. We will have to do this weekly with a larger dataset so automating this is the goal.
Last edited by AliGW; 04-28-2017 at 12:19 PM.
Sorry, noticed a mistake in the above file, please use this one instead (can't find a way to delete it).
Hi,
Your data is not recorded in the most efficient or useful way. The raw data really needs to be held in a normalised 2 dimensional table of unique column Field labels with the rows recording each record. However before being able to advise we really need to understand what you are trying to achieve. Can you explain this in a narrative form. Don't simply repeat what your existing workbook is doing or say how it should be laid out, tell us what basic information you start with and how you need to use it to come up with the results you want to show. i.e. what rules do you apply.
Yes, I know the data is not listed in the best way. That's why I need to reorganize it!It is a wordpress plugin pulling the data and that's what we have to work with - unfortunately I can't change that as it's automatically pulled from a website.
A narrative explanation would be that I have data organized by customer. For example, the first order in the list, order number 840, says that customer 1 is buying four sweet peppers at $5 each from item seller test, one broccoli at $3.59 from item seller test and two cabbage at $3.50 from itemseller test. Not all orders will be solely from one seller though. If you look at the order in row 7, order number 835, that customer has ordered one heirloom tomato for $5 from item seller myonlinemarket, one beef steak for $14 from item seller maggie etc. Since these orders are all arranged by customer, we need a list by item seller. This is for an online farmers market where the farmers will need to bring the total quantity purchased from ALL customers in a given week.
For example, 5 or 6 different customers have ordered broccoli so the seller selling that item needs a total amount of broccoli to bring without having to look through each customer's order. We need to tell him a grand total. That is what I'm trying to accomplish with this macro - a list of orders by farmer, rather than by customer.
I hope this makes sense - sorry I'm not explaining it well. If not, please let me know which part you don't understand so maybe I can try to explain it better.
Cheers!
Is this a simple totalling of vegetable quantities requirement, or does the system need to take into account that customers have preferred farmers that they want to buy from?
And does each farmer provides a unique variety of vegetables? i.e. is any one farmer the ONLY farmer who will supply say carrots and broccoli, and another farmer will only sell peas and apples.
Or are you saying that several farmers may supply the same types of vegetable.
Assuming the simplest case and you just want to collect customer orders and summarise the total quantity of each vegetable/fruit, and not need to worry about matching a particular farmer with a particular customer, are you open to recording your data in a different layout?
No there doesn't need to be preferred farmers. Each order specifies exactly what each customer is buying from which farmer. There is no guesswork. Farmers may sell multiple items similar/the same as other farmers but they will all have a different titles. So if two farmers have carrots they would be called carrots 1 and carrots 2 (or likely "5 lbs of carrots," "2 lbs organic earthwork farm carrots" etc.). There could be a potentially unlimited amount of items - each time, there may be new items added.
Totally open to any other ways of doing this that may be easily replicated by someone with little technical skill since I am setting this up for someone else and this has to be run every week.
So what's the answer to the basic question Is this a simple totalling of vegetable quantities requirements FROM the customers?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks