Help with functions. Need to compare two sets of data to see the difference

**smooth_joe_dee** · 10-13-2019, 09:27 PM

Hi all.

Normally I do my best to work things out myself but I have hit a hurdle. You will see on the test spread sheet I have two lots of data.

The first two columns are the base information.

The next two columns are the data I will keep replacing.

Then I have my totals column.

The situation is I need to know what to order compared to what I Believe I should have in stock.

You will see in the second lot of data it is not the same list as the first. ( sometimes it is missing items and sometimes it has extra in there).

The total column should be B-H but of the relevant company name.

If I have not made it clear please let me know I will try to explain another way.

Thanks for the help in advance.

**alansidman** · 10-13-2019, 09:38 PM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**Logit** · 10-13-2019, 09:39 PM

.
Did you post the correct workbook ? This one has companies and employees.

edit: Sorry Alan ... we posted about the same time.

**smooth_joe_dee** · 10-13-2019, 09:53 PM

Apologies Alan.

I have now amended it however I could not fit everything in there.

**smooth_joe_dee** · 10-13-2019, 09:56 PM

Logit.

Yes I have uploaded the correct data set. This is just a dummy example I am using. One other thing I noticed is the total column will also need to have the employee name relevant to the total.

I have asked in this area as I believe i will need to use conditional formatting to get the desired result.

**Logit** · 10-13-2019, 10:01 PM

.
I'm not certain Conditional Formatting is the answer.

You will most likely need VBA macro or a really good Formula. Admittedly I am not very well versed in Formulas. I will however
study this and see if I can create a macro.

If anyone else has an answer please jump in.

**smooth_joe_dee** · 10-13-2019, 10:23 PM

The way I have been doing it is this.

Step one. Arrange both columns alphabetical

Step two. Use conditional formatting to highlight duplicate values.

Step three. Sort both columns by colour.

Step four. Move all items without a colour to another worksheet.

Step five. Minus H from B to see the difference.

Step six. The rest manually.

As you can see this is a tedious task. The second column I receive every two weeks and it is never the same. There must be a way to copy the second lot of data into a spreadsheet and have it sort all this out. I did think of macros but I haven’t touched them for about 20years.

**Logit** · 10-13-2019, 11:02 PM

.
Ok ... well Excel allows you to record a macro.

You can start the macro recorder ... go through the steps (1 - 5) and then stop the recorder.

Although the resultant macro is not the most efficient coding it will function exactly as you have recorded the steps.

Your step 6 ... not certain what that involves but if is something else that can be automated, you can include that step as well in the macro recording.

**Gregb11** · 10-13-2019, 11:18 PM

This is a little confusing because you talk about items and amount in stock, but then the spreadsheet shows companies and # of employees. If you can mock up the spreadsheet showing consistent data, what it would look like initially, and then the expected results based on the original data, that might help explain it. You also talk about duplicate values - this is duplicate values of what - company name, or company name AND # of employees (or items)? There are no duplicate values in your spreadsheet either.

**smooth_joe_dee** · 10-13-2019, 11:29 PM

Ok. Sorry for the confusion. I’m not that great at explaining my self in digital form. Much prefer the phone or in person. I will redo the data set tonight and repost back in here tomorrow.

I did try the macro recorder and whilst it did do the moves I recorded it still doesn’t do what I need. I’ll explain with pictures tomorrow.

Thank you. Everyone for your help so far.

**smooth_joe_dee** · 10-17-2019, 07:13 PM

Hi all. I apologise for taking soo long to reply but I have had a very busy week. I have now redone the spreadsheet and left comments in it to try and explain better. Please let me know if there is anything else I need to add.

Thank you.

**smooth_joe_dee** · 10-20-2019, 07:10 PM

Sorry for the bump. Just wondering if anyone has had a chance to look at this as I have now uploaded a new spreadsheet.

Thank you

**JeteMc** · 10-24-2019, 05:48 PM

If I understand most of the request then perhaps this will be useful as a base on which to build.
Two helper columns are added to the Core Data sheet to show the corresponding value of the stock item from the EXAMPLE OF WHAT IM SENT sheet (C) and the number needed to order (D).
1. The formula that populates column C is: =INDEX('EXAMPLE OF WHAT IM SENT'!B$2:B$101,MATCH(A2,'EXAMPLE OF WHAT IM SENT'!A$2:A$101,0))
Note that actual name of the sheet on which the current stock level is listed will need to be substituted for 'EXAMPLE OF WHAT IM SENT'
2. The formula that populates column D is: =IF(B2>C2,B2-C2,"")
On the WHAT I NEED TO ORDER sheet:
The formula that populates the stock item column is: =IFERROR(INDEX('CORE DATA'!A$2:A$101,AGGREGATE(15,6,(ROW(A$2:A$101)-ROW(A$1))/(ISNUMBER('CORE DATA'!D$2:D$101)),ROWS(A$1:A1))),"")
The formula that populates the need to order column is: =IF(A2="","",INDEX('CORE DATA'!D$2:D$101,MATCH(A2,'CORE DATA'!A$2:A$101,0)))
Note that the attached is an .xlsx file as your profile states you are using the 2010 version.
Let us know if you have any questions.

**menem** · 10-25-2019, 05:21 AM

Since your profile indicate that you use excel 2010, while your file is .xls.

I've save to .xlsx version for use AGGREGATE function.
And use INDIRECT to avoid refer 'All column' in formulas.

I4

Please Login or Register  to view this content.

I5

Please Login or Register  to view this content.

I7 count for case Minimum stock < Avilable Stock

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter

I8 count for Avilable stock is not presented in CORE

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter

(For I7 & I8 , was try to use only formula that support in .xls , but fail so you can see IF() instead of IFERROR() in the following formula >_<" )

A2 for list part that require to reorder (Avial < Minimum)

Please Login or Register  to view this content.

B2 Qty for re-order

Please Login or Register  to view this content.

And you didn't provide 4th sheet so I've create a 4th_list here.
E2

Please Login or Register  to view this content.

Regards.

**kvsrinivasamurthy** · 10-27-2019, 06:12 AM

here is the formula which works without the help of Helper columns.
In A2 of Sheet1

Please Login or Register  to view this content.

In B2 of Sheet1

Please Login or Register  to view this content.

**smooth_joe_dee** · 10-28-2019, 05:33 PM

I believe you have almost got it menem. Only two problems I have with yours.
1. It won’t work on my computer at work as they only have office 2007. ( I didn’t realise until now and they won’t upgrade.
2. The 4th coloum/ sheet does not have the amounts next to it. If that was in another sheet it would be great.

Thank you for your help so far.

**JeteMc** · 10-31-2019, 08:03 PM

I believe that the two formulas containing the AGGREGATE function are the issue.
The formula in column A on the WHAT I NEED TO ORDER sheet may be written as*:

Formula:

Please Login or Register  to view this content.

The formula in column E may be written as*:

Formula:

Please Login or Register  to view this content.

*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If you want to place columns E:F on a separate sheet select E1:F101 > cut (Ctrl + x) > select a cell on the new sheet > paste (Ctrl + v)
Let us know if you have any questions.

**menem** · 10-31-2019, 09:51 PM

I've change formula in E2 from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for ARRAY formula

Keep old of E2 at J1 as text.

And sorry to reply late. >_<

Regards.

**smooth_joe_dee** · 11-06-2019, 07:38 PM

Hi everyone.

Thank you for all your help. This is now working how I need it to. Everyone’s help is much appreciated.

Can this please be marked as solved. Also if anyone can suggest what I need to learn to be able to do this myself. I have tried backwards engineering the formulas given but am unable to work it out.

Thank you again.

**JeteMc** · 11-07-2019, 09:28 AM

You're Welcome and thank you for the feedback. As the original poster of this thread you are able to, and please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Help with functions. Need to compare two sets of data to see the difference

LinkBack

Thread Tools

Rate This Thread

Display

Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions

Re: Help with functions

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Re: Help with functions. Need to compare two sets of data to see the difference

Thread Information

Users Browsing this Thread

Similar Threads

Need to loop through a set of functions and access the changing variable in the functions

Creating multiple IF Functions with some nested IF functions in false value return

VBA – User Designed Functions (UDF) - Renaming array functions

Excel 2007 : Calling functions based on input values in Excel 07 (functions array?)

AVERAGEIFS, SUMIFS and COUNTIFS Functions New functions in Excel 2007

efficiency: database functions vs. math functions vs. array formula

[SOLVED] Conversion from Spreadsheet Toolkit functions (ESSV....) to EssBase API functions

Bookmarks

Bookmarks

Posting Permissions