Forum Discussion
Excel Journal Entry Automation
Hello all!
I'm submitting this request realizing I've hit the limit of my excel knowledge but would still really like to be able to consolidate this retrieve into a format I can copy/paste into a journal entry template. I'll try my best to step-by-step explain what I want to achieve.
The first tab labeled "APR-25 Unbillable Entry" is where I would want my formulas to go (columns E, G, & H) (picture of the general layout below)
The second tab labeled "Activity retrieve" is the retrieve for the numbers I'd like consolidated (picture below)
The first tab is much larger than the picture shows but I'm hoping if I can get a proof of concept working for this section, I can apply it for the rest of the file.
Here is what I would like the first formula going in column E to do:
- View column B (these numbers change in the whole file) and compare with F1:J1 on the 2nd tab
- Once it finds the correct column (for this example it will be column F), I want it to view the entire column until it finds a value that is not 0, if it only views 0 I would like it to return nothing
- If it finds a value that is not 0, I would like it to compare column B of the 2nd tab with column D of the first tab (this makes sure it is attributed to the correct account and I only want the formula to return a value with the matching account
- Once it finds both of those - I would like it to return column D of the 2nd tab without the "AT" part of the string attached
- Example: Column B shows 1636 -> views column F on 2nd tab -> finds value in row 24 for AC6274 -> returns "9U" in E4 on tab 1
The 2nd part of this request will be for columns G & H on tab 1:
- If there is a value (in this case for column F24) I will need it to show in the correct column
- If value is positive, I will need it to populate for the corresponding account in column G, if it is negative it should populate in column H (these should populate on the same line as column E if it populates)
- Once again, if there is no value it should populate nothing. The value should also only populate in either columns G or H not both.
- If the value is negative it should show as a positive number, but in column H. See example for further clarity.
- Example: Value in line 24 shows (1,229.12) -> negative value means column H -> G4 on tab 1 should show nothing -> H4 on tab 1 should show 1,229.12
I really appreciate anyone who took the time to read all of this and anyone who takes even more time to help find a solution I promise I will reply and upvote the solution :)
Let me know if I can provide any more specifics that would be helpful!
unfortunately it requires a login:
but the good news is that it does appear you have excel 365 since it isn't giving a name? error.
I also wanted to ask what that 'Sub' column was for since it is 000 in each case (at least in the example). It might be easier if we could make a single formula to spill all 3 columns (or all 4 and include the 'Sub' column)
EDIT: I think I found the issue. In my sample I have the CC column as numbers which match the numbers in row 1 of the Activity retrieve sheet. But I think you have them as TEXT. if you convert those text values to numbers or vice versa the numbers to text it should work. Alternatively we can update the formulas to force both to text (or numbers):
column E:
=MID(FILTER('Activity retrieve'!D:D, ('Activity retrieve'!B:B=Sheet2!D2)*(N(CHOOSECOLS('Activity retrieve'!F:J,XMATCH(Sheet2!B2&"",'Activity retrieve'!$F$1:$J$1&"")))<>0),""),3,9)column G:
=IF(E2="","",LET(c,CHOOSECOLS('Activity retrieve'!F:J,XMATCH(Sheet2!B2&"",'Activity retrieve'!$F$1:$J$1&"")),o,FILTER(c, ('Activity retrieve'!B:B=Sheet2!D2)*(N(c)<>0),""), q,-12,p,CHOOSE(SIGN(o)+2,HSTACK("",-o),{"",""},HSTACK(o,"")),p))in the updated attached file I include these formulas and also a version that has all 4 columns spill:
=LET(c,CHOOSECOLS('Activity retrieve'!F:J,XMATCH(Sheet2!B10&"",'Activity retrieve'!$F$1:$J$1&"")), r,XMATCH(1,('Activity retrieve'!B:B=Sheet2!D10)*(N(c)<>0)), ac,MID(INDEX('Activity retrieve'!D:D,r),3,9), v,INDEX(c,r), p,CHOOSE(IFNA(SIGN(v)+2,2),HSTACK(ac,"000","",-v),{"","000","",""},HSTACK(ac,"000",v,"")), p)so it first picks the column,
then finds the row (r)
based on the row it finds the AC and the value (v)
then p just creates the 4 cell output.
this could also be wrapped into a larger LAMBDA using either stacking in a REDUCE function or using THUNKS but for now lets just get this part working
9 Replies
Thanks for sharing this
How about this:
For Column E:
=IFERROR(INDEX('Activity retrieve'!D:D, MATCH(1, ('Activity retrieve'!B:B=B4) * ('Activity retrieve'!F:F<>0), 0)), "")For Column G:
=IF(E4<>"", IF('Activity retrieve'!F24>0, 'Activity retrieve'!F24, ""))For Column H:
=IF(E4<>"", IF('Activity retrieve'!F24<0, ABS('Activity retrieve'!F24), ""))Hi Kidd_Ip I attempted this solution and it didn't seem to get quite what I was looking for. Column E seems to return nothing and G&H display false. Below is the picture of the result and formulas I used for row 4.
Column E:
=IFERROR(INDEX('Activity retrieve'!D:D, MATCH(1, ('Activity retrieve'!B:B=B4) * ('Activity retrieve'!F:F<>0), 0)), "")Column G:
=IF(E4<>"", IF('Activity retrieve'!F24>0, 'Activity retrieve'!F24, ""))Column H:
=IF(E4<>"", IF('Activity retrieve'!F24<0, ABS('Activity retrieve'!F24), ""))Thank you for your reply!
alternatively try
column E:
=MID(FILTER('Activity retrieve'!D:D, ('Activity retrieve'!B:B=Sheet2!D2)*(N(CHOOSECOLS('Activity retrieve'!F:J,XMATCH(Sheet2!B2,'Activity retrieve'!$F$1:$J$1)))<>0),""),3,9)column G:
=IF(E2="","",LET(c,CHOOSECOLS('Activity retrieve'!F:J,XMATCH(Sheet2!B2,'Activity retrieve'!$F$1:$J$1)),o,FILTER(c, ('Activity retrieve'!B:B=Sheet2!D2)*(N(c)<>0),""), q,-12,p,CHOOSE(SIGN(o)+2,HSTACK("",-o),{"",""},HSTACK(o,"")),p))note this requires Excel 365 and column G will 'spill' into column H
In reviewing Kidd_Ip equations I made some corrections to automatically select which column to use and re-find the correct row for the subsequent columns but keeping what I believe will work for an old version of Excel in mind and believe the following would work in that case:
column E:
=MID(IFERROR(INDEX('Activity retrieve'!D:D, MATCH(1,('Activity retrieve'!B:B=D2)*(INDEX('Activity retrieve'!F:J, 0,MATCH(B2,'Activity retrieve'!$F$1:$J$1,0))<>0),0)),""),3,9)column G:
=IF(J2="","",IFERROR(1/(1/MAX(0,INDEX('Activity retrieve'!F:J,MATCH(1,('Activity retrieve'!B:B=D2)*(INDEX('Activity retrieve'!F:J,0,MATCH(B2,'Activity retrieve'!$F$1:$J$1,0))<>0),0),MATCH(B2,'Activity retrieve'!$F$1:$J$1,0)))),""))column H:
=IF(J2="","",IFERROR(1/(1/MAX(0,-INDEX('Activity retrieve'!F:J,MATCH(1,('Activity retrieve'!B:B=D2)*(INDEX('Activity retrieve'!F:J,0,MATCH(B2,'Activity retrieve'!$F$1:$J$1,0))<>0),0),MATCH(B2,'Activity retrieve'!$F$1:$J$1,0)))),""))both options are in the attached file (the later 3 are in columns J,K,L)
Hi m_tarler, thank you for your help! I attempted all both strategies in different ways and I couldn't seem to get it to work for me. I even attempted copying the formulas from the excel sheet you provided while updating the tab name of Sheet2 and I was unsuccessful. I'll provide the errors for all 3 solutions with pictures and the formulas I used. I'll just provide formulas for row 4 where we'd expect an output for simplicity
The first grouping of formulas gave me #N/A errors:
Column E:
=MID(FILTER('Activity retrieve'!D:D, ('Activity retrieve'!B:B='APR-25 Unbillable Entry'!D4)*(N(CHOOSECOLS('Activity retrieve'!F:J,XMATCH('APR-25 Unbillable Entry'!B4,'Activity retrieve'!$F$1:$J$1)))<>0),""),3,9)Column G:
=IF(E4="","",LET(c,CHOOSECOLS('Activity retrieve'!F:J,XMATCH('APR-25 Unbillable Entry'!B4,'Activity retrieve'!$F$1:$J$1)),o,FILTER(c, ('Activity retrieve'!B:B='APR-25 Unbillable Entry'!D4)*(N(c)<>0),""), q,-12,p,CHOOSE(SIGN(o)+2,HSTACK("",-o),{"",""},HSTACK(o,"")),p))The 2nd group of suggestions for old excel didn't populate anything for column E, G, or H.
Column E:
=MID(IFERROR(INDEX('Activity retrieve'!D:D, MATCH(1,('Activity retrieve'!B:B=D4)*(INDEX('Activity retrieve'!F:J, 0,MATCH(B4,'Activity retrieve'!$F$1:$J$1,0))<>0),0)),""),3,9)Column G:
=IF(E4="","",IFERROR(1/(1/MAX(0,INDEX('Activity retrieve'!F:J,MATCH(1,('Activity retrieve'!B:B=D4)*(INDEX('Activity retrieve'!F:J,0,MATCH(B4,'Activity retrieve'!$F$1:$J$1,0))<>0),0),MATCH(B4,'Activity retrieve'!$F$1:$J$1,0)))),""))Column H:
=IF(E4="","",IFERROR(1/(1/MAX(0,-INDEX('Activity retrieve'!F:J,MATCH(1,('Activity retrieve'!B:B=D4)*(INDEX('Activity retrieve'!F:J,0,MATCH(B4,'Activity retrieve'!$F$1:$J$1,0))<>0),0),MATCH(B4,'Activity retrieve'!$F$1:$J$1,0)))),""))Really appreciate your help once again! Really unsure what I could be incorrectly inputting to make these formulas not work. I also attempted to attached a condensed version of the workbook but not sure I have the ability to.
