Gst the vertical amounts in horizontal order to respective columns

[RAJESH SHAH]

Hi,
I have a very complicated formula to write and if needed edit the existing formula to get the expected result. Each voucher is separated by a blank line in B2B. I want to write each voucher in one line horizontally be it 1 row or 4 rows in a single voucher.
I have applied formulas in Purchases and got the value of only the first-row value of B2B correct. If there are multiple lines in a voucher, I am not able to get the values of the remaining rows in the connected amounts columns.
The rows in bold are the total of the above row / s which is not to be taken into account. Hope someone can help with the formula.
I think I will wait for a perfect formula and If it is not possible with a formula, then I will have to go for a VBA code.
Sorry, I forgot to change the invoice numbers. The worksheet is showing the same invoice number for each voucher. Each voucher Number is different for every voucher under One GSTIN.
One GSTIN of supplier can not have the same invoice number.

[Limor_OP]

Are you using excel 2007?

[RAJESH SHAH]

I am using excel 2019. But as I am trying to create an app. with formula / code which will be compatible to all versions will be a great help.

[JohnTopley]

Try

in C2

Please Login or Register  to view this content.

in E2

=INDEX(B2B!$B$7:$B$100,MATCH(Expected!$C2,B2B!$C$7:$C$100,0))

and similar for A:F

in G2

Please Login or Register  to view this content.

in S2

Please Login or Register  to view this content.

and bad design to use 1.5 etc rather than 3, 5 etc as it is not obvious that these refer to values of 3, 5


I used AGGREGATE but this is not avaiable in 2007: use INDEX/SMALL as alternative

I did not understand formula for AC so I will leave that to you.

[RAJESH SHAH]

Thanks John Topley. A very complicated formula to work on. Let me check. I will revert back.

[RAJESH SHAH]

This is the 3rd time I designed this to get it right. 1.5 is not connected to the B2B sheet. It takes the values from the result sheet from column H and M as per condition applied in the formula. AC to AG, actually columns R to AG refer to the result sheet and get the value from B2B sheet if conditions met.
I just checked your formula and it is showing the correct results. I will try to use your formula in all the other columns too and hopefully I will do it. It is going to take time. Will revert back and will post if I am not able to get the values of any column.

[RAJESH SHAH]

Getting Column A values becomes priority as you have given column A as reference in all your formulas. So getting the values in column A to be solved first. Without that I will not be able to get the values in other columns.

[JohnTopley]

Column A = Column E (as per your own sample) and there is no "Tally Date" in B2B !!!!

And "Invoice Number" is the unique Key not date so I do not understand your reasoning.

For column S:T

=IF(H2="","",IF(B2B!K7=0,B2B!L7))

=IF(H2="","",IF(B2B!K7=0,B2B!M7))

will not work

[RAJESH SHAH]

Tally Date and Invoice Date have the same dates. So if you get the invoice date you can give a formula in Tally data as = B2.
The invoice date in B2B whose font is bold to be taken in Expected sheet one below the other which I think will be possible only with a code.
Columns R to AH will be auto calculated once the columns A to Q are filled correctly.
Only if you can get the INvoice date in column B with the help of a formula I can get the data of the remaining columns easily. Till then, I will try once again try to get the date using vlookup invoice number and try to get the dates. A few months ago, I had come across some formula where vlookup will work in any direction. Will have to look for it and try it out.
.

[JohnTopley]

Invoice Date/Tally date :

=VLOOKUP($C2,B2B!$C$7:$E$100,3,0)

Nothing difficult here!

[RAJESH SHAH]

Yes I did that and got both the Invoice date and tally data. The Invoice number and Voucher number are one so I got that too. Trying to get the remaining columns. The one with the formulas to get the amounts in the respective columns is the most difficult part.

[RAJESH SHAH]

I am trying to get the trade name with vlookup. As it is on the left side of the Invoice number I am using this formula I came acroos some time ago.
I edited the range and sheet name accordingly but it ain't working. Can you please help me to correct it.

Please Login or Register  to view this content.

I am getting error in most of the rows as the format must be different in Invoice number. The same errors I am getting in column A B and E.

[RAJESH SHAH]

Attachment 799841
The Invoice number is correct but maybe due to the format it is showing an error as in the image. If you overwrite the same invoice number in the rows with error it will accept and show the result without error.

[RAJESH SHAH]

My formulas in the columns S to AG ain't working. I think I will have to use an index match function to get the values of all the amounts in the respective columns.
Your formulas in post $4, for G2 and S2 is working for the first row only. If I drag it down I am getting all blanks. Need to get the index formula for column M which is pending.
Will try again tomorrow.

[JohnTopley]

Look at G12:G14 in the file I posted where there are results..

As for S2, it is the same formula as in S:AB where multiple values are shown: over to you.

[RAJESH SHAH]

Sorry John Topley. I was busy and had a hectic day. So, I was not able to reply. We shall continue tomorrow.

[RAJESH SHAH]

John Topley, Your formulas are working only if the invoice numbers are unique in each column. As I told you earlier that the invoice number of One supplier can not be the same but there are instances where the invoice number of different suppliers can be the same. The suppliers are identified by their GSTIN numbers. I checked a new sample data where the invoice number are the same of different suppliers. So, this formula will not work which you have entered in Invoice number column.
Can you please match the invoice number with the GSTIN number and then get the values in all the other columns with the index match function. ?
This is the new data I tested upon. The only problem in this is that where the invoice number is same the values extracted are showing in both the yellow as well as green columns. It should show in either of these columns.
I have added this one extra column GSTIN of supplier in the Expected result sheet so that it will be easy to match invoice number as well as GSTIN number together and get the index match of values of all other columns correctly.

[JohnTopley]

If you need to match on GSTIN

Column A

=IFERROR(INDEX(B2B!$A$7:$A$100,AGGREGATE(15,6,ROW($B$1:$B$96)/(RIGHT(B2B!$C$7:$C$100,5)="Total"),ROWS($1:1))),"")

Adjust INDEX/Match to reference column A not column D

[RAJESH SHAH]

I will have to match with invoice number as well as with GSTIN number to get the correct result. The GSTIN will appear multiple times in column A and I will face the same issue as I am facing with Inv0ice Number.
Something like match invoice number and GSTIN and then get the Invoice value.

[JohnTopley]

I'll leave that to you!

[RAJESH SHAH]

Yup. I am still trying.

[RAJESH SHAH]

John Topley. Phew! Finally got the trade name corrected with the right formunla. Now working on the formulas from column H to AG. I have replaced the formulas but some of them ain't working right. Please see if you can help me to correct them. I am also trying to get the right formulas. With your help and support we should be able to solve this. In the 2B sheet, Columns H to R are Taxable Values derived from in B2B sheet. You need to add another condition in the formula to match the rate column I of the B2B sheet with the headings of 2B sheet from H to R.

[JohnTopley]

formula does check H:R vs I

Please Login or Register  to view this content.

[RAJESH SHAH]

I can't understand why the red colored cells are filled then. Maybe the formula where the cells are freeze need to be corrected.

[RAJESH SHAH]

Can you Match the GSTIN and Invoice number instead of Date to get the values in the formula ?

[RAJESH SHAH]

I tried to edit your formula in post #23 and replaced <>"" with =0, but still I am getting all cells blanks. I also changed the match from date to GSTIN. I hope you are best at editing and will leave it to you. The ball is in your court. See you tomorrow. Time to hit the sack. If you have any more doubts please let me know. I will be online for another 1/2 hour.

[JohnTopley]

Try

=IFERROR(INDEX(B2B!$J$7:$J$100,AGGREGATE(15,6,ROW($B$1:$B$100)/(B2B!$A$7:$A$100=$A2)/(B2B!$C$7:$C$100=$D2)/(B2B!$K$7:$K$100=0)/(B2B!$I$7:$I$100=H$1)/(B2B!$I$7:$I$100<>""),1)),"")

IMHO , you should have been able to do this

[RAJESH SHAH]

It is perfect man.

[RAJESH SHAH]

John Topley. It is time for me to hit the sack. I will try again tomorrow. These are the 2 conditions to get values in columns S to AG.
Get values in Columns S to AB if the values in columns N to R =0
Get Values in columns AC to AG if the values in columns H to M =0
Good Night man.

[RAJESH SHAH]

John Topley. I have got the values of all the columns from H to AG correct. The query is solved now. Thanks a lot for your help.

[JohnTopley]

Glad to hear

Well done to you for trying to, and succeeding, in updating the formulas: you learn much more by "DIY".

And thank you for the rep.

[RAJESH SHAH]

I try to do as much as I can but a few complicated formulas and codes are too difficult for me. So, without help from good Samaritan like you, I just can't complete it. Later I try to understand the complicated formulas and practice them.