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

ExcelAssistance

New Member
Joined
Jan 21, 2018
Messages
8
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Glad to help & thanks for the feedback
 
Upvote 0
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 :(
 
Upvote 0
<user group=""></user>Wrong
 
Last edited:
Upvote 0
Ok, how about
Code:
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>
 
Last edited:
Upvote 0
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.
 
Upvote 0
Starting with this


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


After the code in post#15 I get


Excel 2013 32 bit
AB
1CarsToyota
2CarsChevrolet
3CarsFord
4FruitApple
5FruitBanana
6Music
7MoviesTitanic
8MoviesDie Hard
9bb4:g:jrjendjendiTitanic
10@simplesolutionTitanic
11cg2-grp-mgmt-fill_blue
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top