VLookup to determine data between location of cells

[cstoreguru]

I have searched and searched threads and can't seem to find what I am looking for. The problem may be that I am an amateur and cannot convert what others have done in to what I am trying to do.

The data is a copy of CSV file in to a spreadsheet. Below is what the paste looks like:

100100 Books
1234.ab
1345.bf
1456
1567
100100 Books
100200 Magazines
100300 Videos
1234.ab
1345.bf
1456
1567
100300 Videos

All of the numbers between the 100100 Books, 100300 Videos belongs to that category. This repeats hundreds of rows down. I need the column to the left of this range to display which division it under. I parse this in excel so Books, Videos, Magazines will be in their own column. I am not exporting the CSV so have to manipulate it in excel. I need to have the column to the left combine the whole identifier so I can perform a VLOOKUP on it. It should look as follows:

100100.1234.ab
100100.1345.bf
etc.

My approach was to get the column to the left of the range to display the correct division, then have another column combine by using (cell)&"."&(cell).

I apologize if this has been addressed. I am trying to learn VB for excel, if I could get some help I would be forever grateful and will return the favor when I have a clue of what I'm doing. Thanks!!

 

[Stoneboysteve]

cstoreguru,

Is the data in a single column? Are the values 100100 Books, 1234.ab, ect in single cells? Is the data imported- you said it is a CSV?

I am not grasping your data structure, can you please explain what is in columns and rows, and how you are going to use the vlookup? Are you parsing within the same spreadsheet or another workbook? Are you intending to build VB scripts to automate this for repeated actions?

Stoneboysteve

 

[cstoreguru]

Stoneboysteve,
Thank you so much for your inquiry. I am sorry that I was unclear.

All of the data is in one column. It looks exactly like I posted. Except there are dollar amounts three columns to the right of the data posted. I need these dollar amounts in my Primary Workbook.

Values 100100 Books, 1234.ab, etc, are in single cells. So for instance, A1 contains the value "100100 Books". Each value listed is in exactly once cell. The data is a copy from a csv export. It is pasted in to PrimaryWorkbook. Primary Workbook already has the categories and subcategories in it and I am attempting to copy the data from the pasted csv report at the end of each month. The problem is that in the PrimaryWorkbook, the names for the main categories (100100 Books) and the subcategories (1234) and the child of the sub-categories (ab) are combined. The main category and its sub-categories are separated by a period.
It looks like this in the PrimaryWorkbook.

100100.1234.ab
100100.1345.bf
etc...

What I would like to do is to write a function that will recognize that the subcategories are between the main categories and put the main category value in the column left of the subcategory. This way I at least know that I can use a separate column to combine the main category, sub-category, and child-category all in to one value.

Once I have done this, I can use a vlookup to look at my existing category names and search this pasted data for the correct value to the right of it. The report changes monthly, as does the location of each category.

I hope this brings some clarity to my situation. Please let me know if there is anything that I can do to make this more clear. I am desperate for some input on this, so thank you so much!

 

[Stoneboysteve]

cstoreguru,

Please send me a private message through the forum with your spreadsheets- take out any sensitive data. Since the forum is a learning opportunity I will post the solution by posting the code. I tried to replicate your question and response, but I cannot get my hands around what you are really trying to achieve. I believe I can solve the technical requirements. To send a private message, point the mouse over my user name and select the action.

Stoneboysteve

 

[cstoreguru]

It appears that I cannot send you a message because I don't have 15 posts. I also can't attach an excel file here. Is there another way I can get this spreadsheet to you?

 

[muckshifter]

It appears that I cannot send you a message because I don't have 15 posts. I also can't attach an excel file here. Is there another way I can get this spreadsheet to you?

sorry, this is in place to help us combat spam ... Ian can grant you the rights needed.