Update monthly sales with New accounts

[msgrant]

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows 98

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

A1 =


A B C D E F
1 Ac No Area Name Jan-04 Feb-04 Mar-04
2 A014 5 Cobham Fluid Systems Ltd 1528.92 0.00
3 A015 5 Ampohm Capacitors Ltd 0.00 230.60
4 A021 4 Assemtech Europe Ltd 0.00 47.41
5 A027 3 AVX Ltd 0.00 0.00
6 A087 3 ASL Ltd 168.61 102.90
7 A098 3 Antrim Transformers Ltd 0.00 0.00
8 A107 3 Alphasonics 0.00 0.00
9 A117 5 Active Electronics Plc 0.00 1050.00
10 A120 4 Megger Ltd 2455.00 1541.80
11 A125 4 Air Care Products 0.00 1600.00
12 A138 5 Applied Weighing 0.00 435.00
13 A141 5 Antares (Europe) Ltd 320.25 320.25
14 A147 5 Applied Polymer Technology 374.00 0.00
15 A181 5 Alansons Industrial Supplies 498.62 105.06
16 A188 5 Automation Sales Ltd 0.00 0.00
17
18 Ac No Area Name Mar-04
19 A015 5 Ampohm Capacitors Ltd 230.60
20 A021 4 Assemtech Europe Ltd 47.41
21 A040 5 Arcol UK Ltd 109.04
22 A057 5 Arun Electronics Ltd 640.50
23 A141 5 Antares (Europe) Ltd 320.25
24 A181 5 Alansons Industrial Supplies 105.06
25 A204 4 Ami-Con Supplies Ltd 2007.91
26 A207 4 Aerospheres (UK) Ltd 177.09
27 A212 4 Alroy Microwave & Electronics 15.00
28 A233 5 Aquatec Electronics Ltd 1362.50
29 A237 6 Albion Chemicals 2260.40
30 A238 5 Aero Stenraw Ltd 108.45


I have a spreadsheet as above for sales how can i
1 compare col A to see of new account and if so insert line
2 update Mar 04 col with new fig including 0.00 when no sale
been cutting pasting but so long winded

Please help

 

[msgrant]

i have used html maker but how do i post this so it appears on the created sheet or does the code genereted convert back when posted

 

[msgrant]

Can someone tell me wot is wrong so i can rectify so far nearly 40 been but no comments at all

 

[Smitty]

Welcome to the Board!

The HTML code that is generated is converted to an HTML image when you post it. Click the button in the new browser window that is generated -->Click to send image to the clipboard.

In your post, just hit CTRL+R, type your message, then submit.

As for your question, do you have a master customer list to compare to or is this it?

Smitty

 

[msgrant]

Tried 84210 link refered by Wayne but this is my result any idea where I am going wrong

mr excel1.xls
	A	B	C	D
1				0
2			0
3	A014	A015	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
4	A015	A021	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
5	A021	A040	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
6	A027	A057	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
7	A040	A141	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
8	A057	A181	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
9	A087	A204	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
10	A098	A207	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
11	A107	A212	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
12	A117	A233	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
13	A120	A237	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
14	A125	A238	IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")
Sheet1

 

[msgrant]

As no reply i am thinking of deleting this post everyday i look and am getting disheartened as no ome tells me wot is wrong wiv my post

 

[PaddyD]

You have not specified your problem in sufficient detail, and your snapshot of your sheet doesn't seem to relate to your original problem.

Having got the html maker working, use it again to post up a representative snapshot of your data. In the statement of your problem, include references to the data you're posting & a detailed example. have a read of the "Posting tips" thread for some views on how to maximise your chances of getting a decent answer:
http://www.mrexcel.com/board2/viewtopic.php?t=49751

 

[msgrant]

[HtmlMaker 2.42]

SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

Workings.xls
	A	B	C	D	E	F	G
1	YTD
2	Ac no	Area	Name	Jan	Feb	Mar	Apr
3	A014	5	Cobham Fluid Systems Ltd	323.92	1384.04	1169.32	1528.92
4	A015	5	Ampohm Capacitors Ltd	0.00	1111.50	0.00	0.00
5	A021	4	Assemtech Europe Ltd	331.00	501.50	83.75	0.00
6	A027	3	AVX Ltd	67.90	0.00	0.00	0.00
7	A034	5	Ampohm Ltd	0.00	0.00	549.35	0.00
8	A040	5	Arcol UK Ltd	0.00	0.00	0.00	0.00
9	A050	6	Astralright Ltd	85.51	0.00	166.50	0.00
10	A056	5	AGI Ltd	440.50	(63.00)	0.00	222.92
11	A057	5	Arun Electronics Ltd	0.00	0.00	0.00	0.00
12
13	Current
14	Ac no	Area	Name	May
15	A015	5	Ampohm Capacitors Ltd	230.60
16	A021	4	Assemtech Europe Ltd	47.41
17	A040	5	Arcol UK Ltd	109.04
18	A057	5	Arun Electronics Ltd	640.50
19	A087	3	ASL Ltd	102.90
20	A117	5	Active Electronics Plc	1050.00
21	A120	4	Megger Ltd	1541.80
22	A125	4	Air Care Products	1600.00
23	A138	5	Applied Weighing	435.00
24	A141	5	Antares (Europe) Ltd	320.25
25	A181	5	Alansons Industrial Supplies	105.06
26	A204	4	Ami-Con Supplies Ltd	2007.91
27	A207	4	Aerospheres (UK) Ltd	177.09
Sheet1

--------------------------------------------------------------------------------



Try again
My problem is that I have two sheets YTD & Current
I am looking to compare column A from both sheets and then copy the lower A/c No to the next available line on a new sheet until all have been copied only once and in order A14,A15 etc I was thinking of first inserting zeros in column D E F G on current then do above so as to update monthly figs at the moment I trawl manually thru about 1500 lines on YTD compare visually to current & cut, paste and insert blank row where necessary for any new accounts do i need to first merge the two together sort & subtotal or can someone help with a less time consumating solution

Matthew

 

[msgrant]

What do i need now to get help on this everyone looks and no tells me what they think or if something is lacking I`ve tried the Holy Macro disc tried to find VBA on there But dont know how to adapt the macro i found

Sub SearchReplacing()
Dim var As Variant
Dim lngRow As Long
Dim intSheet As Integer
For intSheet = 2 To Worksheets.Count
With Worksheets(intSheet).Range("A1").CurrentRegion
For lngRow = 1 To .Rows.Count
var = Application.Match(.Cells(lngRow, 1), Columns(1), 0)
If Not IsError(var) Then
Range(Cells(var, 1), Cells(var, 3)).Value = _
.Range(.Cells(lngRow, 1), .Cells(lngRow, 3)).Value
End If
Next lngRow
End With
Next intSheet
End Sub

to my application

 

[George J]

I'm not sure what you are looking for.

Why are you comparing the 2 sheets? Is there something we are looking for in one that is not in the other?

Or do you want us to add the current details to the YTD so that if in June Account no. 58 comes along it will enter itself below 57 but keep the month details to June?