bring row data to a coulm based on a Reference Bill no. and party

**srinivasan1965** · 03-22-2016, 08:05 AM

HI

I sincerely thank this forum for helping me at various stages
I need a help now
We are using this format for sales tax return filing purpose.

The raw data is available original data. It is listed as per item billed in the respective bill no.
Some times a bill may contain one item some time more than 20 items.
Freight transport charges are also billed.
As a purchaser of commodity we need to club transport charges to take respective row of the item and show add to cost of purchase.
We use combining bn and supplier to arrive unique ref no and do vlookup countifs for bringing transport
charges. Against one of the bill reference.
In case two transport charges are billed for the same bno. It needs to be totaled and bring as total freight against a particular reference no
Some times this list may be exhaustive during peak completion of a project 2000 to 3000 records a month

is it possible to automate through coding
This may be use ful especially if i am handing over this job oridinary user.
A excel file is attached
Thanks in advance for your help.
N.Srinivasan

**jaslake** · 03-22-2016, 12:32 PM

Hi N.Srinivasan

Try this Code in the attached. CTRL + x will fire the Code.

Please Login or Register  to view this content.

**srinivasan1965** · 03-23-2016, 12:19 AM

Hi
Thanks for your help.
I am unable to see any content on output sheet Perhaps i may be wrong in understanding.
I hope this code shall create combining Bill no & supplier as unique refe Now I have deleted this colum for testing purpose.
I hope I need not mark * on every item " Transport Charges"
Tested file is attached.

Pls help me to understand.

N.Srinivasan

**jaslake** · 03-23-2016, 12:29 AM

Hi N.Srinivasan

Sorry about that. The Code assumes this already exists...easy enough to create...I'll get back to you.

code shall create combining Bill no & supplier as unique refe

**jaslake** · 03-23-2016, 01:03 AM

Hi N.Srinivasan

This Code in the attached creates

Bill no & supplier as unique refe

As before CTRL + x will fire the Code.

Please Login or Register  to view this content.

**srinivasan1965** · 03-23-2016, 02:59 AM

Hi
Thanks for your efforts.
Content in the output file is not generated
can you please help.

Regards
N.Srinivasan

**srinivasan1965** · 03-23-2016, 10:06 AM

Hi Jaslake
Any help on this Please

**jaslake** · 03-23-2016, 10:36 AM

Hi N.Srinivasan

Are you running the Code on the File attached to my post #5 or a different File?
When you open the File attached to my Post #5, enable Macros, press CTRL + x.
On my platform, results are in the attached.

**srinivasan1965** · 03-24-2016, 12:22 AM

Hi
I am following the same procedure. I have dowloaded the file deleted the contents of transport and out put.
Contents of transport are created. However content for output sheet is not created.
I am able to run other previous macros
Is there any version releated problem I am using excel 2007.

Pls help
Thanks & Regards

**jaslake** · 03-24-2016, 10:19 AM

Hi N.Srinivasan

There's only one thing I can think of that could be causing this and I've modified the Code for that. If this fix does not work for your platform I'm at a loss.

content for output sheet is not created

Please Login or Register  to view this content.

**srinivasan1965** · 03-24-2016, 11:59 PM

Hi Jaslake
It works superb.Thanks you very much Now the contents in output sheet are created.
Now the code assumes we are combining the bill No and suppliers in a colum. Ok Not a problem
only thing is if 2 or morte 3 items are listed in a particular Bill. transport amount is repeated twice Is it possible to arrest this.
Please refer the row no.18 original data one more item is added in this bill. In the output Rs.993 transport is replicated twice.
Sorry to Bother you too much Please help.
Thanks & Regards
N.Srinivasan

**jaslake** · 03-25-2016, 10:46 AM

Hi N.Srinivasan

I apologize, I attached an outdated file to my post #10.

Code in the attached does the following:

Creates the Bill number and Supplier to create unique identifiers.
Eliminates the duplication of Transport Charges
Adds the Transport Charge to the Tot Amt Column.

Let me know of issues.

**srinivasan1965** · 03-26-2016, 12:16 AM

Hi
I am only taking help from you You are spending your valuable time for me. Thank you very much. I feel embarssed when you use words like apologise and all.
Now coming to the issue again the content of output file is not created Though sheet transport created with content.
Pls help

**jaslake** · 03-26-2016, 11:03 AM

Hi N.Srinivasan

I don't know...performs as expected on my platform. Please attach the offending File.

**srinivasan1965** · 03-27-2016, 11:53 PM

Hi
The offended file is attached for your checking
Pls help

Thanks
Srinivasan

**jaslake** · 03-28-2016, 11:30 AM

Hi Srinivasan

There's only a small section of Code that could be causing this aberrant behavior...I've modified those lines yet again, taking a slightly different approach.

Please Login or Register  to view this content.

Edit: What version of Excel are you running? Your User Profile indicates Excel 2007.

**srinivasan1965** · 03-29-2016, 03:30 AM

Hi Jaslake

I am using windows professional 7 excel version 2007
Your post 10 is working perfectely excepting You may please modify the code for Bno supplier(It should assume there it is not available in the original file and if items are repeated for the same bno Tranposrt amount is totalled against the first reference of Bill no of the respective item
Thank you in advance.
Thanks & Regards
N.Srinivasan
T

**jaslake** · 03-29-2016, 10:02 AM

Hi Srinivasan
The Code does do this; it's created in Column G of each worksheet, used, then deleted. Do you NOT want it deleted from ANY of the worksheets?

Bno supplier

Regarding this...

if items are repeated for the same bno Tranposrt amount is totalled against the first reference of Bill no of the respective item

it should look like this?

Row\Col	A	B	C	D	E	F	G	H	I	J
17	32	920	29/01/2015	Rahul Marbles & Granites	16	Granite Slabs	90222	7266	993	98481

I'd suggest you mock up what you wish the three worksheets will look like for your Final Requirements and attach that Workbook.

**srinivasan1965** · 03-30-2016, 01:57 AM

Hi Jaslake,
I give below the procedure we presently follow and working file.
1)Original data shall be COLUM A TO COLUMI
2)Combine b& D in F coulm
3)Insert coulm after F and paste special
4) Delete formula based b& D ie coulm
4) Filter Item containing transport and copy to another sheet
5) Now Delete all Transport charges item
6)Insert countif coulm give formula
7) Vlookup with transport sheet for bringing transport charges
8)copy paste special transport charges
9) Erase #N/a
10)Filter countif result with 2 and above erase all transport charges manually duplicates
11)Transport charges repeated twice for a particular ref871Rahul Marbles & Granites

12) Add manually the additional transport charges in the ist item.
13 We tally the vlooked trasnsport charges and transport sheet figures
14)We need to show original sheet, Transport sheet, out put
Output File contain originaldata after erasing transport charges+corresponding freight charges incorporated against the respective bill ref
No need of showing comibing bno&ref or other working.Let other working shall run in back end.

Please help vba coding for this Attached please fine excel file
Thanks & Regards

**jaslake** · 03-30-2016, 09:36 AM

Hi Srinivasan

Row 18 of your Final Output Shows this

Row\Col	A	B	C	D	E	F	G	H	I	J
18	34	871	10/01/2015	Rahul Marbles & Granites	17	Granite Slabs	3900	566.00	4466	1134

Is that correct or should it be this

Row\Col	A	B	C	D	E	F	G	H	I	J
18	34	871	10/01/2015	Rahul Marbles & Granites	17	Granite Slabs	8900	566.00	9456	1134

**srinivasan1965** · 03-30-2016, 10:32 AM

Hi
The first table is right. Only freight amount is to be added. Otherwise all other items are to be refelected by item by item only

Thanks

**jaslake** · 03-30-2016, 11:01 AM

Hi Srinivasan

In your Original Data, what will Column A look like? Column E? Just as presented?

In your Original Data Column A has some blanks; Column E has repeating numbers.

**srinivasan1965** · 03-30-2016, 12:02 PM

Hi
colum a is a copying error only This will have serial number for rows.
colum e is salestax regn number of the vendor. I have given here dummy data. some party may be having blank for this. This sales tax regn number will vary from vendor to vendor.
Eg Ragul Marbles will have one number
Vaigai sanitition shall have another number.
if supply from vaigai sanitation is repeated this number shall get replicated again
Hope it clarifies
Thanks & Regards

**jaslake** · 03-30-2016, 12:22 PM

Hi Srinivasan

I have Code that appears to do as you require...it APPEARS to work on your Sample File. However, in order to find this Record and delete it I'm relying on the Value of Column E.

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

19

871

1/10/2015

Rahul Marbles & Granites

18

Granite Slabs

5000

Manually erase

So, I need to see what your ACTUAL Original Data looks like. Just the ACTUAL Original Data, no other Worksheets required.

Based on your description of Column E values, chances are pretty good that the Code WILL NOT work on your ACTUAL Original Data.

**srinivasan1965** · 03-31-2016, 12:01 AM

Hi
Please find sample original file

Thanks & Regards
N.Srinivasan

**jaslake** · 03-31-2016, 10:59 AM

Hi N.Srinivasan

I'm still trying to find a "hook" to identify that errant record on line 20 (serial # 19). The only thing I see different about it is there is no entry Tot Amt. Will this ALWAY be the case?

I can make the Code work except for identifying that (those, if several) record(s).

Row\Col	A	B	C	D	E	F	G	H	I	J
1	sn	bn	bdate	vendor	tiin	Item	Bn&Supplier	base value	Tax	Tot Amt
19	18	871	10/01/2015	Rahul Marbles & Granites	24371267904	Granite Slabs	871Rahul Marbles & Granites	3900	566.00	4466
20	19	871	10/01/2015	Rahul Marbles & Granites	24371267904	Granite Slabs	871Rahul Marbles & Granites	5000
21	20	871	10/01/2015	Rahul Marbles & Granites	24371267904	Transport charges	871Rahul Marbles & Granites	1034	0.00	1034
22	21	871	10/01/2015	Rahul Marbles & Granites	24371267904	Transport charges	871Rahul Marbles & Granites	100		100

**srinivasan1965** · 03-31-2016, 12:39 PM

Hi Jaslake,
Please note that there will a total amt for each and every item. I am sorry I made a dummy item to make you to understand a scenario.I missed to copy total for the above item.
regards

**jaslake** · 03-31-2016, 04:32 PM

Hi N.Srinivasan

I'm unable to find anything unique about the Record on Line 19 in order to delete it. This is about as far as I can go...sorry.

Row\Col	A	B	C	D	E	F	G	H	I	J
1	sn	bn	bdate	vendor	tiin	Item	base value	Tax	Tot Amt	Transport
17	16	920	29/01/2015	Rahul Marbles & Granites	24371267904	Granite Slabs	50111	7266	57377	993
18	18	871	10/01/2015	Rahul Marbles & Granites	24371267904	Granite Slabs	3900	566	4466	1134
19	19	871	10/01/2015	Rahul Marbles & Granites	24371267904	Granite Slabs	5000	0	5000
24	28	938	03/02/2015	Rahul Marbles & Granites	24371267904	Granite Slabs	21915	3178	25093	1807

**srinivasan1965** · 04-02-2016, 01:08 AM

Hi All
can anybody forward to help me out on this. I am requesting please

Thanks & Regards
N.Srinivasan

bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Re: bring row data to a coulm based on a Reference Bill no. and party

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro copying rows to coulm based on specific criteria

[SOLVED] How to bring back data based on a range of numbers

Bookmarks

Bookmarks

Posting Permissions