Need Macro to Compare Columns and then Reorganize Rows

**WildSpreadsheets** · 09-09-2011, 01:16 PM

I have this spreadsheet where I am trying to get a macro to compare column b with a and if they match, to move column b under column a to reorganize a gigantic spreadsheet. Any ideas?

I have an example attached

**MarvinP** · 09-09-2011, 01:28 PM

Hi WildSpreadsheets and welcome to the forum,

I guess I need a sample workbook to play with to understand this problem.
Looking at your example, Column B would be copied under column A twice for Eggs and one more time as it has a Juice in it. Do you use all/any value in column A to match any value in Column B to decide if to copy it?

You can attach a sample by clicking on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area.

**WildSpreadsheets** · 09-10-2011, 12:44 PM

Hi MarvinP!

Thank you for your quick reply! Sorry for my late follow-up ... I have created a better example for you to look at in my excel spreadsheet to give you the exact idea I have in mind.

I want the macro to reorganize the jumbled example on the left and make it look like the one on the right.

Sorry for all of the messy explanations.. I tried to explain it the best I can and it is a problem that is kind of hard to explain!

Thank you for your help!

**MarvinP** · 09-10-2011, 01:53 PM

Hi WildSpreadsheets,

Your question is close to a relational database sorting method. Parent and Child relationships are needed to solve your problem. Look at sheet 2 where I've added a d'Aboville System to your data and sorted it accordingly. See http://en.wikipedia.org/wiki/Genealo...bering_systems for styles of numbering.

In short I have no idea how to build the numbering system on sheet 2 column A using Excel on the attached.

Maybe one of the smart Guru's can figure it out. I'll think about it a while but this might be a problem Excel isn't well suited to do.

Maybe that is why you've named yourself WildSpreadsheets?

**WildSpreadsheets** · 09-10-2011, 03:03 PM

Hi MarvinP!

I thought about using that numbering system as well so that I can just sort things in order. This works for a shorter list of data that is constant and manually managed

My only problem is that every week my spreadsheet contains randomly pulled data that is unorganized, so I cannot use the same numbering system. Which poses the problem that you stated, which is figuring out how to build the numbering system for the random parent/child-matching of data so that it can be sorted accordingly.

I am dealing with about a thousand "Parents" with multiple "Child" relationships which go as deep as 7 levels.

This is exactly why I go by WildSpreadsheets!...

**WildSpreadsheets** · 09-12-2011, 09:51 PM

I may be making this sound more complicated than it really is. Can a Macro just compare two columns and then move the entire rows under the matching columns accordingly?

**MarvinP** · 09-12-2011, 11:03 PM

It is true that this problem may be easier than we think. The problem is, we're not thinking those simple thoughts.

I've pondered this answer about 5 times in the last few days without a good answer. If we knew who the first parent was it might be easier. It seems to me it has to be determined from simply having two colums of names (foods in your case) and building the tree from them.

I am sorry I don't have a good answer, yet.

Lets see if any other gurus weigh in and have an answer.