Help please

[Lizzi]

Hi all, i think i'm having a senior moment and need some assistance plse.

im using excel 2016, i have a data set that will possibly change from week to week and i'm trying to track the changes on certain things. On my raw data table i have added previous status and previous amount. i have copied the data from the previous week in to a separate sheet within the workbook and here comes my senior moment...i cant for the life of me think what i need to do next to track the change, if any....
Column A is an id field, col B is opp/lead. i want it to look at column A to see the id, then look at column b and see if it says oppy or lead then look at previous week to see if the same id says either oppy or lead and to see if the $ amount has changed from the previous week

ID oppy/lead. Amount previous week previous week amount
1234 oppy 50,000 lead 75,000
5678 oppy 50,000 lead 40,000
abcd lead 50,000 lead 50,000

so i can see on the example that id 1234 was previously a lead for 75K but this week its changed status to an oppy but dropped by 25K, where as id 5678 changed from a lead to an oppy but increased by 10K

any help appreciated.....

Thanku
Lizzi

 

[Joe4]

I fail to see what effect "oppy" or "lead" has to do on anything.
It seems to me that you subtract your previous week amount from your current week amount, i.e.

=C2-E2

Is this issue that some records will not have previous week amount, so you don't want to do the subtraction in that case, or vice versa?
If so, simply check columns B and D for values first, i.e.

=IF(AND(B2<>"",D2<>""),C2-E2,"")

 

[Lizzi]

I fail to see what effect "oppy" or "lead" has to do on anything.
It seems to me that you subtract your previous week amount from your current week amount, i.e.

=C2-E2

Is this issue that some records will not have previous week amount, so you don't want to do the subtraction in that case, or vice versa?
If so, simply check columns B and D for values first, i.e.

=IF(AND(B2<>"",D2<>""),C2-E2,"")

the lead or oppty will change, it will likely go from lead to oppy and thats what i need to know also. The amount column will always have a value, it may or may not change thou

 

[Joe4]

Can you please post a sample of data showing different situations/combinations and your expected results in each case.

Please do not post the sample like you did in your original post. Unformatted data like that gets all smashed together making it very difficult to read.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you cannot use that, you can post images.

 

[Lizzi]

Can you please post a sample of data showing different situations/combinations and your expected results in each case.

Please do not post the sample like you did in your original post. Unformatted data like that gets all smashed together making it very difficult to read.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you cannot use that, you can post images.

sorry i didnt expect it to go like that, my apologies, i've attached an image, the ID has to be used to make sure i'm capturing the correct info, so its a unique id.

 

[Joe4]

OK, you should be able to get the values for columns E, F, and G by using VLOOKUP formulas and looking up the IDs shown in column A from the other sheet.
(see this if you are not sure how to do that: Excel formula: VLOOKUP from another sheet | Exceljet)

Then, you can just place the following formulas in cells I3, J3, and K3 and copy down:
I3: =A3
J3: =IF(AND(F3="Lead",B3="Oppy"),"moved to an oppy","")
K3: =IF(G3=C3,"no change",C3-G3)

 

[Lizzi]

OK, you should be able to get the values for columns E, F, and G by using VLOOKUP formulas and looking up the IDs shown in column A from the other sheet.
(see this if you are not sure how to do that: Excel formula: VLOOKUP from another sheet | Exceljet)

Then, you can just place the following formulas in cells I3, J3, and K3 and copy down:
I3: =A3
J3: =IF(AND(F3="Lead",B3="Oppy"),"moved to an oppy","")
K3: =IF(G3=C3,"no change",C3-G3)

thanku, i shall try it out later