I have changed some of the data to give you an example of what I need.
There are only three fields that i am interested in one table. I want to update my main report with a field containing the data in the third field with the data which is specific to the first two entries. But vlookup doesnât seem to like it
Item Code
Rocko
September 15, 2015, 2:33pm
202
Is it based on both the first two columns or do you consider them separately? I donât really see what your issue is.
So are you looking up 50059698 or 50029671
No, it is based on the first two columns.
The first column is the item code and the second is a subsequent code generated in the process. Technically the second code should be unique but multiple entries took place referencing that number, hence why there are multiple entries for it for some.
I need a formula which will look at both columns and and then apply the third column to the report when there is a match for the first two
Ring an Indian mate. Heâll sort that in jig time for you and save you the stress.
1 Like
Rocko
September 15, 2015, 7:19pm
205
Have you any problem adding a helper problem.
Iâd suggest inserting a new column C (in between B and D) with =A1&B1 etc all the way down. That will give you the combination of those two.
Then do your lookup on column C.
=vlookup(C1,C1:D50000,2,false)
Is this what youâre getting at?
If you canât insert a column there but can add one at the end then you would need to do index & match to do the lookup.
ironmoth
September 15, 2015, 10:33pm
206
Vlookup can give an error if the item you are looking up is not in the first column of the array.
(I just spent 20 mins reading through this thread - some of the responses you got are hilarious pal!)
Rocko:
Have you any problem adding a helper problem.
Iâd suggest inserting a new column C (in between B and D) with =A1&B1 etc all the way down. That will give you the combination of those two.
Then do your lookup on column C.
=vlookup(C1,C1:D50000,2,false)
Is this what youâre getting at?
If you canât insert a column there but can add one at the end then you would need to do index & match to do the lookup.
@Big_Dan_Campbell ⌠did this work?
Besides this thread, where would one get a good grip on the basics of excel and making charts/reports from data.
Also, anyone have experience using/developing bobj reports?
Besides this thread, where would one get a good grip on the basics of excel and making charts/reports from data.
Also, anyone have experience using/developing bobj reports?
youtube
anything you want youtube has it
https://www.youtube.com/watch?v=7yvUIzDqoAs
1 Like
Ok this is a very easy one for ye guys
So I have two columns
E.g.
If I wish to remove one or more lines from Column A how do i set it up so that column B automatically rebalances to 100%
Sum Column A. Then on the first cell on Column B, divide the adjoining cell in Column A by the total cell using absolute reference (ie. if itâs cell A6, type it as $A$6). It should look something like =A1/$A$6.
Drag this formula down, and hey presto.
1 Like
ironmoth:
Sum Column A. Then on the first cell on Column B, divide the adjoining cell in Column A by the total cell using absolute reference (ie. if itâs cell A6, type it as $A$6). It should look something like =A1/$A$6.
Drag this formula down, and hey presto
I am with you as far as Sum Column A
2 Likes
Yeah, rereading my post I can see how I lost you.
I normally use excel only as a calculator tbh
1 Like
Christ! Give me a minute. Weâll nail this.
PS. Did you really work in a bank? Fuck me, but that explains a lot
3 Likes
gilgamboa:
So I have two columns
Column a- weekly costs
Column b - breakdown of costs in column a as percentages the total of which is 100%
E.g.
Costs %age
5000 25%
10000 50%
5000 25%
If I wish to remove one or more lines from Column A how do i set it up so that column B automatically rebalances to 100%
Do you have to submit a spreadsheet to your wife with your weekly expenses so she will continue your housekeeping money ?
6 Likes
Step 1
Step 2
Step 3
2 Likes
Cheers. So if i deleted A5 it would automatically readjust the %ages to come to 100%?
Whats story with the $ symbols
