LinkBack URL
About LinkBacks
I want to Copy adjustment from Trial balance as mentioned in excel file. COuld you please help me with how to copy those adjustment in simple way. I have attached excel file. the issue and the result I want to achieve.
I want to Copy adjustment from Trial balance as mentioned in excel file. COuld you please help me with how to copy those adjustment in simple way. I have attached excel file. the issue and the result I want to achieve.
I just want to copy each line in which in front of those lines there is a figure. So both line and figures to be copied to below with the help of formula.
Maybe someone can come up with an easier way but I did it with ARRAYED FormulasIn Col A (A19 of Sheet2)...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(MOD(ROWS($A$19:$A19),3)=0,"",INT((ROWS($A$19:$A19)-1)/3)+1)
In Col B (B19)
=IF(MOD(ROWS($A$19:$A19),3)=0,"",IF(MOD(ROWS($A$19:$A19),3)=1,"Dr", "Cr"))
In Col C, Arrayed formula
=IFERROR(IF(B19="Dr",INDEX( $C$2:$C$17, MATCH(TRUE, ISNUMBER(INDEX($D$2:$Q$17, ,A19)),0)), INDEX($C$2:$C$17, MATCH(2, 1/(INDEX($D$2:$Q$17, ,A19))))),"")
In Col D, Arrayed formula
=IFERROR(IF(B19="Dr", INDEX($D$2:$R$17, MATCH(TRUE, ISNUMBER(INDEX($D$2:$R$17, ,A19)),0), A19), INDEX($D$2:$R$17, MATCH(2, 1/ISNUMBER(INDEX($D$2:$R$17, ,A19))), A19)),"")
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thank you Chemist B. It really Help. Could you pls also tell me if in one coloumn there is 3 figures, what I should change in array formula, so that it consider.
For Ex: Intangible 400
PPE -300
inventory -100
Could we make formula like, to find itself how many number of figures in one coloumn, it consider whether it is Dr or Cr and then do the same thing as you showed in formula
The current formula won't do. I'll need to rethink it.
I have put new excel related with this issue. Your help really will help me to save a lot of time
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks