Organize multiple rows and columns of data into a database format

[smd71092]

Hello all, I have some data in my Excel file that is formatted like this (the actual file is much larger but follows this exact format with an empty row in between each of the "Items"). See attachment for a clearer format.

Customer1 Customer2 Customer3
Item1 (like/no pref/dislike) Like Like Dislike
Amount of Item1 bought 27 5 1
Item2 (like/no pref/dislike) No pref Like Dislike
Amount of Item2 bought 5 5 2
Item3 (like/no pref/dislike) Dislike No pref Like
Amount of Item3 bought 9 1 17

Is there any formula or macro that would be able to output this into a database friendly format? What I want is to have the customer name in the first column, the item name in the second column, whether or not the customer liked/disliked or had no preference to the item in the third column and finally how many of the item they bought in the fourth column so it would look something like this.

Customer Name Item Like/no pref/dislike Amount bought
Customer1 Item1 Like 27
Customer1 Item2 No pref 5
Customer1 Item3 Dislike 9
Customer2 Item1 Like 5
Customer2 Item2 Like 5
Customer2 Item3 No pref 1
Customer3 Item1 Dislike 1
Customer3 Item2 Dislike 2
Customer3 Item3 Like 17

I am familiar with the OFFSET function but couldn't think of a way for it to work here so it seems like this would have to be a macro although I would prefer a formula. But if there is no formula possible for this, I will repost in the VBA/Macro section. Any help at all would be appreciated!

[dflak]

It seems to me I solved this problem twice this week with slightly different formats. Attach a sample workbook and I'll see if I can adapt the code.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[smd71092]

Sorry about that! I didn't know the formatting would look so weird after I posted the question. I took your advice and made a simple sanitized file that shows exactly how the data is organized in a BEFORE tab (in the BEFORE tab, the blank rows in between the items is how the data is currently organized so hopefully you can account for that as well) and then I gave an example of what I want it to look like in the AFTER tab. Hope this helps!

[dflak]

This isn't organized like the ones I saw earlier this week, but the before and after really make it clear on what needs to be done.

I have several questions. Is there a limit to the number of items in column B or is it open ended?

If it is open ended can I depend on the data being consistently organized as Item on one row, Amount of Item on the next row, followed by exactly one blank row? If not, are there key words I can look at in column B to indicate where an item's data begins?

Will the phrase (Like/no pref/dislike) always appear and I assume you want it dropped?

I'll assume that the number of customers is variable and that it's always customer information (like/quantity) followed by optional comments that you don't want to see in the final data.

[dflak]

I pressed on with my assumptions and came up with the following:

[smd71092]

You mean in the AFTER tab? There is usually a limit but it is different for each store. There are about 50 items in this one example but I do believe they will change from store to store so it won't always be exactly 50 items they range from 30-75.

And yes the data in the BEFORE tab is always in this format you see (i have updated it to add an extra row called "times visited store to buy Itemx". So it will be Item on one row, Times visited store to buy Itemx next, then Amount of item on next row and exactly one blank row followed by the next item. Since the items all have different names there isn't really a way to look at key words.

The "like/no pref/dislike" in the BEFORE tab that appears in Column B after the Item1, Item2 and Item3 is actually just my way of illustrating that it is a dropdown menu and isn't actually shown near the Item so you can assume that text isn't there at all (so you can take exactly what is in that cell without editing it first). If it helps I have attached an updated workbook that is more accurately like my current workbook.

And yes you are correct the number of customers is extremely variable and the comments should be skipped. I just want the first column data of each customer.

[smd71092]

Sorry it appears I responded just a few minutes too late, are you able to quickly redo it to work as I have shown in the revised file? And exactly how does the macro work? Do I just run it and the data appears in the Data tab?

[dflak]

Just run the macro and it will put the data in the data tab. If you need to change the sheet names, then change these two lines of code:

Please Login or Register  to view this content.

[smd71092]

Just run the macro and it will put the data in the data tab. If you need to change the sheet names, then change these two lines of code:

Please Login or Register  to view this content.

Fantastic!! This has just saved me so much work. Thank you so much. I appreciate all the help!

[dflak]

Glad to be of help - please mark as solved.