Thread: Match & Align VBA

Hi everyone,

I have no experience with VBA programming and I'm hoping that someone can help me. Here is the example situation:

I have a spreadsheet that contains inventory information. One of the columns has a SKU number I have defined to track the products. I have another spread sheet with SKUs of items that have sold. I need to match the two columns, then align the SKUs. I need the matching SKU to be aligned to the same row. If a SKU does not match, I need a way of identifying it.

I've attached an example XLS file. The first column will be where I placed the SKUs from a different spreadsheet. The data in all columns except the first are associated with the SKU in column "SKU". It would be best if only the cells in the column "To Be Matched" be the only cells that move when aligned.

I hope someone can help me. A VBA like this would save me so much time. I have hundreds if not thousands of entries. Doing it by hand will take forever, and I most likely will make errors.

No problem.

To make it simple, I have two databases, that don't cross talk. My sales associates use an online base program to create and list auctions to ebay. Then I need to access each individual auction, to gather data, and create a listing for our website.

The problem is, due to the way ebay exports their data. Auctions that are "Fixed Price" or buy it now, cannot be isolated. The only way for me to get these auctions in any list is to export all Active listings. Because of this issue, I have a lot of extra listing data that I need to filter. I've been able to filter out the real auctions. So I'm left will all fixed price auctions. However some of these fixed price auctions have already been listed onto the website. I need to figure out which ones have not been listed.

I found VBA which lets compare me the CSV upload SKUs (for website inventory) to the CSV export SKUs (from ebay) to see which fixed price auctions I have on the website and which I need to list. The resulting list of SKUs are from the CSV export (from ebay) which need to be uploaded.

This is where the current VBA helps. I need it to align the generated list of SKUs back to the SKUs from the CSV export. When I align the SKUs I'll have the rest of the data associated with those SKUs to quickly copy and paste into a CSV file for upload to the website.

Essentially there is data that makes me life much easier in the CSV export from ebay. But I need to filter out the erroneous listings and isolate the listings that are fixed auction and have not been listed on our website.

In the attachment, I moved the Item ID column to the right of the SKU column.

Then I selected A1:B1, and typed keys in the Names box (left of the Formula bar).

Then did Alt+F8, selected AlignKeys (a macro I added to the workbook) and pressed Run.

Originally Posted by shg

In the attachment, I moved the Item ID column to the right of the SKU column.

Then I selected A1:B1, and typed keys in the Names box (left of the Formula bar).

Then did Alt+F8, selected AlignKeys (a macro I added to the workbook) and pressed Run.

Close! I took your spreadsheet and copied in the data. Nearly 1500 listings. Then I took the filtered SKUs that I needed to align and placed it in the "To Be Matched" column. Ran the macro, and BAM! It aligned all the SKUs in the "To Be Matched" column to the "SKU" column.

However, it placed all the SKUS at the bottom of my spreadsheet. I need to everything to stay in place. Because the rest of the spreadsheet contains information associated with the SKUs in the "SKUs" column. That is really the information I'm hunting for.

I think the hardest part about this VBA macro is keeping all the cells in all the columns stationary, except for the cells in the "To Be Matched" Column. Its extremely vital that all that information stays associated to its proper sku in the "SKUs" column. That is the information I need to mine out of the spreadsheet.

Yup, sorry, that's not the way it works. It puts both sections in alpabetical order, then inserts spaces in one section of the other until like rows align.

I did find this sample sheet somewhere on the internet. I'm not sure how to edit the VBA to make it fit my needs. However it does do exactly what I need.

Edit: Fixed the attachment. The file I first attached was already aligned.
Also looks like its the same or of the same VBA code you used. Its just that the list it compares to doesn't move. I dunno how they did that.

It's the exact same code.