Mar 06 2019 02:45 PM
Hello! I want to make a cell in H1, where within a certain matrix I've made, whenever the number 1 appears in row E, the cell H1 should copy the number in row C. This I know how to do by now.
However the problem is that there is multiple times where the number 1 appears, therefore I would like that the first time the number 1 appears, write it in to cell H1, when it appears next time, write it into cell H2 etc. - Is there any way I can do this? Perhaps a function I can copy down the area where I would like it to fill in? Thanks in advance!
What I have so far is: =VLOOKUP(1;B:E;2;FALSE)
Mar 06 2019 07:45 PM
Mar 07 2019 06:02 AM
Thanks alot for your response, I have tried this formula and it seems to be working exactly like wanted! I have just 1 problem left, it seems to be adding a value of 0 in each of the sides I've put it. (I've changed the code a tiny bit, to make it fit my needs. I have tried attaching pictures of what it looks like, with the formula where there the number 0 appears. Hope you can help me! Thanks alot again! :)
Mar 07 2019 06:18 AM
Sorry here are the pictures
Mar 07 2019 06:48 AM
Also, I have one last question here - I would like to do so that the Account name automatically changes depending on what account name is referenced as 1. Therefore I tried to do: "=VLOOKUP(1;A:D;1;FALSE)" Indicating it should give me the text one the same row as the number 1, however I get an error. I believe it's because the number 1 occurs more than once, and I'm only asking for one value. Therefore I would like for it to add only the text from the first time 1 occurs, or any of the times it occurs, just only once. Is that possible?
Mar 07 2019 07:06 AM
Mar 07 2019 07:57 AM
How fortunate! I appreciate you taking your time to help me.
I have attached this document, please bear in mind that I am very new at this, however I found this project quite interesting and learning. Therefore, I do not necessarily just want an answer to my solutions by alot of random codes, but would rather be able to understand what has been done, atleast at some point.
I would like to see your way of achieving this, and if possible I would appreciate alot if you would be able to change the code for me on the third tab for my vlookup function so that it instead of saying e.g. "Credit side" it would simply skip to the next value. I assume there must be some sort of function to replace instead of writing "Credit side". (I'm not searching for a new function in the whole, just a function to replace at the point where I have written "credit side" so that it skips this value).
I hope that you can look through my mess, I am simply trying out alot of things to learn at the moment.
Mar 07 2019 08:43 AM
Mar 08 2019 08:01 PM
In the attached file, the formulas are as follows:
1. To assign a unique Reference to each Account, the formula starting in E2 is:
=IF(COUNTIF(B$2:B2,B2)=1,
MAX(E$1:E1)+1,
VLOOKUP(B2,B$1:E1,4,0))
2. To create a distinct list of Accounts, sorted in ascending order, the formula starting in G2 is:
=IFERROR(LOOKUP(PI(),
1/(COUNTIF(AccountsEntered,">="&AccountsEntered)=MAX(INDEX(
COUNTIF(AccountsEntered,">="&AccountsEntered)*(COUNTIF(G$1:G1,AccountsEntered)=0),0))),
AccountsEntered),"")
3. To create a list of sequential numbers corresponding to the number of entries to the selected Account in J3, the formula starting in I5 is:
=IF(ROW()-4>COUNTIF(AccountsEntered,J$3),
"",
SUM(I4,1))
4. To create a list of Debit entries to the selected Account in J3, the formula starting in J5 is:
=IFERROR(IF(LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
DebitsEntered)=0,"",
LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
DebitsEntered)),"")
5. To create a list of Credit entries to the selected Account in J3, the formula starting in K5 is:
=IFERROR(IF(LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
CreditsEntered)=0,"",
LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
CreditsEntered)),"")
6. To calculate the balance of the selected Account in J3, the formula in K3 is:
=ABS(SUM(DebitEntries)-SUM(CreditEntries))
Note that all named ranges are dynamic, such that you can add (or delete) entries without need to modify any of the formulas.