Creating a macro to sort data

arosboro · #1 (**permalink**) 05-12-04, 15:40

I'm not really experienced with Excel. I understand programming logic though and can probably handle the code for a macro. I have two excel files that I need to use to gather some information. In the first file are product and component IDs in one column and quantities in another column. The second file contains an exported table from Microsoft SQL that has a column called Pf_ID which is product IDs and another column called In_Stock which has outdated stock quantity information. I guess you can ignore In_Stock.

I need a macro to look for each Pf_ID in the first excel file and put that row in another database or sheet or something. Basically I want the first excel file with the product and component IDs to contain just product IDs which are found in the second file along with thier updated quantities.

Can someone please give me an example, explain the basics, or if it's easy enough give me a macro to handle this? I don't really know if it would be possible to do what I want using "Record New Macro".

Thanks

arosboro · #2 (**permalink**) 05-17-04, 16:13

I guess I could do this by taking workbook A and removing all rows from workbook B that have equal values in the first column of book A. Once I had a seperate list of components and products I could easily seperate the two, no matter what was in the other columns.

Can someone please offer up help in this? My knowledge of Excel Macros isn't good enough.

shades · #3 (**permalink**) 05-17-04, 16:30

It sounds as if you are wanting to use a lookup formula of some type. If so, set that up first. Then, you can record a macro, and when you get to the portion where you include the formula, you can just type in a simple formula into the cell, then click outisde the cell. Then stop the recording. Then go back into the VB Editor window and look for that formula, and replace it with the actual formula. Keep in mind that there is a difference between using Excel formulas/functions and using VBA functions/formulas.

arosboro · #4 (**permalink**) 05-18-04, 15:43

can't someone help me out with actual macro code or formulas here? Is there a really good book w/ examples on all kinds of things you can do with advanced macros in excel?

akamp · #5 (**permalink**) 05-21-04, 12:34

Could you insert the sheets (or a truncated version thereof) so that we can look at the data? I'm having a difficult time visualizing what it is exactly you're trying to do. I'm assuming the ID's are somewhat similar between the 2 files, if they were the same you wouldn't need to do anything.

arosboro · #6 (**permalink**) 05-21-04, 16:54

Here is the worksheet with products and components and updated quantities.

Code:

05.123N000	0
5.142	0
1	4
10000	-5
10001	30
10002	14
10003	-1
10004	-25
10005	-11
10006	-42

This is the SQL database which only containts products and out dated quantities.

Code:

Pf_ID	Name	Description	Feature	UnitCode	Package_Qty	Prod_Weight	List_Price	Sale_Price	Sale_Start	Sale_End	Attr_Label1	Attr_Label2	Attr_Label3	Attr_Label4	Attr_Label5	Manufacturer_ID	With_Image	Image_File	Image_Width	Image_Height	In_Stock	Location	Doc_Name	Visible	Receivable_Account	Inventory_Account	GL_Account	Sales_Account
10000	IMRC10	Interface Module,  10-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	2	0.5	1568	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10000.pdf	1				
10001	IMRC14	Interface Module,  14-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	2	0.5	1792	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10001.pdf	1				
10002	IMRC16	Interface Module,  16-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.5	2016	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10002.pdf	1				
10003	IMRC20	Interface Module,  20-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.5	2184	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Raging	Connector Type	Number of Positions			1		0	0	100	G2-8	10003.pdf	1				
10004	IMRC26	Interface Module,  26-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.5	2288	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10004.pdf	1				
10005	IMRC34	Interface Module,  34-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.5	2756	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10005.pdf	1				
10006	IMRC40	Interface Module,  40-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.5	3224	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10006.pdf	1				
10007	IMRC50	Interface Module,  50-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.75	3692	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10007.pdf	1				
10008	IMRC60	Interface Module,  60-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.75	4704	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10008.pdf	1				
10009	IMRC64	Interface Module,  64-pin Ribbon Cable,  Fixed Terminal Block	"High quality connectors and terminal blocks
<BR>Locking ears hold cable in place
<BR>Marking helps to simplify wiring
<BR>Component holes standard on all modules
<BR>Fixed or pluggable terminal blocks
<BR>Industrial high quality appearance"	EA	1	0.75	4944	0	1/1/2000	1/1/2000	Wire Range (AWG)	Current Rating (A)	Connector Type	Number of Positions			1		0	0	100	G2-8	10009.pdf	1

10000 + are products, everything else is a component. I just need a sheet/workbook with only products and quantities.

Internet Services

Web Development

Digital Marketing

Consumer Tech