Macro to offset at specific tags and label them based on prior tag

[ExcelAssistance]

Hi guys,

Just looking for a little vba assistance. I'm sure this one has been asked but wasn't sure how to describe it to search for it. Basically if I had a sheet where Column A is populated as follows:

1: Fruits
2: Apples
2: Bananas
2: Pears
1: Vegetables
2: Broccoli
2: Peas

and what I'd like is for the vba to go through the entire column and everytime it finds something with a 1 preceeding it, it looks at all the 2's below and shifts those 2's into column b and places the same 1 grouping in column a...so the above would appear as follows:

1: Fruits 2: Apples
1: Fruits 2: Bananas
1: Fruits 2: Pears
1: Vegetables 2: Broccoli
1: Vegetables 2: Peas

How would I go about doing that? Everything either has a 1 or a 2 before it (they aren't 1's and 2's but they are tags just the same) to define it and ideally I'd also like to strip the tags off as well (and also eliminate the original thing tagged as 1 since it now appears to the left of the item it defines) so really it would just be column a and b looking like

Fruits Apples
Fruits Bananas
Fruits Pears
Vegetables Bananas
Vegetables Peas

Can anybody let me know how to do this? Thanks in advance for any assistance you can give.

 

[ExcelAssistance]

It works beautifully. Thank you soooo much fluff. Appreciate you donating your time to help the rest of us.

 

[Fluff]

Glad to help & thanks for the feedback

 

[ExcelAssistance]

I'm sorry fluff, I actually still hit a road block. The code worked perfect on the test data I was trying it on, but in the live worksheet I hit a problem.

A few of the cells with the user group tag have special characters and obscure data in them. Whenever it hits one of these it basically just stops doing the .replace.

Some examples would be
< user group >bb4:g:jrjendjendi
< user group >@simplesolution
< user group >cg2-grp-mgmt-fill_blue

At first i thought it was simply because om two of them they look like cell references, but even replacing them so they aren't cell combinations like bb4 isn't fixing the issue. I'm assuming the first one my have everything to do with the : being used by Excel define ranges etc and trying to turn it into a formula may be problematic, but I'm having a tough time figuring out a workaround?

Any thoughts? Sorry

 

[Fluff]

<user group=""></user>Wrong

 

[Fluff]

Ok, how about

Sub RearrangeData2()
   
   Dim Ar As Areas
   Dim Rng As Range
   
   Columns(1).Insert
   With Range("A1", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .Value = Evaluate("if(left(" & .Offset(, 1).Address & ",9)=""<user id="">"",""X"","""")")
      Set Ar = .SpecialCells(xlConstants).Areas
   End With
   Rows("1:" & Ar(1).Offset(-2).Row).Delete
   For Each Rng In Ar
      Rng.Offset(, 1).Cut Rng.Offset(-1, 2)
      If Rng.CountLarge > 1 Then Rng.Offset(-1, 1).FillDown
   Next Rng
   Columns(1).Delete
   With Columns(1)
      .SpecialCells(xlBlanks).EntireRow.Delete
      .Replace "< User Group ><user group="">", "'", xlPart, , , , False, False
      .Replace "< /User Group ></user>", "", xlPart, , , , False, False
   End With

   Columns(2).Replace "< User ID ><user id="">", "", xlPart, , , , False, False
   Columns(2).Replace "< /User ID ></user>", "", xlPart, , , , False, False
End Sub

This will insert a new col A as a helper & then delete it afterwards.</user>

 

[ExcelAssistance]

Hmm this just shifted everything into the helper column, threw some x's into column a anytime a blank space appeared in column B and got another app/object error right after the first with statement (rows("1:"...)

I deleted the junk data above and when I did that it didn't error out but it essentially deleted all the data I was looking to isolate.

 

[Fluff]

Starting with this

Excel 2013 32 bit
	A
1	junk
2	junk
3	junk
4	junk
5	junk
6	junk
7	junk
8	junk
9	junk
10	junk
11	junk
12	junk
13	junk
14	junk
15	junk
16	junk
17	junk
18
19	Cars
20	Toyota
21	Chevrolet
22	Ford
23
24	Fruit
25	Apple
26	Banana
27
28	Music
29
30	Movies
31	Titanic
32	Die Hard
33
34	bb4:g:jrjendjendi
35	Titanic
36
37	@simplesolution
38	Titanic
39
40	cg2-grp-mgmt-fill_blue
Sheet1

After the code in post#15 I get

Excel 2013 32 bit
	A	B
1	Cars	Toyota
2	Cars	Chevrolet
3	Cars	Ford
4	Fruit	Apple
5	Fruit	Banana
6	Music
7	Movies	Titanic
8	Movies	Die Hard
9	bb4:g:jrjendjendi	Titanic
10	@simplesolution	Titanic
11	cg2-grp-mgmt-fill_blue
Sheet1