Excel VBA to transpose set of rows if value exists in another column

Question

I'm trying to find a way via VB script that will transpose rows from column A into a new sheet but only if there is a value in column B for rows that contain numbers. I have a sheet with ~75K rows on it that I need to do this for, and I tried creating pivot tables which allowed me to get the data into its current format but I need the data to be in columns.

The tricky part of this is that in column A, I only need to look at the rows that are all numbers and not the other rows that have text.

I created a sample sheet to view, where the sample data is in the SOURCE tab and what I want the data to look like in the TRANSPOSED tab.

https://docs.google.com/spreadsheets/d/1ujbaouZFqiPw0DbO78PCnz25OY2ugF1HtUqMg_J7KeI/edit?usp=sharing

Any help would be appreciated.

If you can provide an example of what you have tried so far, we might be able to help you. — luke_t
– luke_t, Commented Feb 24, 2016 at 14:34
EDIT: I found a similar post, which is very close to what I am trying to do but the major difference is that this post's sample data has valid data in every row whereas mine needs to look for only rows that contain all numbers and then look to see if there is a value in column B. stackoverflow.com/questions/12962828/… — ivdezine
– ivdezine, Commented Feb 24, 2016 at 14:43
@lturner - I started to do it manually via copy/paste special transpose, but quickly realized that with the amount of data this would take forever. And, I'm certain I'll have to do this again at a future time so wanted to ask here since I don't have much experience in writing VB or really advanced formulas. — ivdezine
– ivdezine, Commented Feb 24, 2016 at 14:48

ivdezine · Accepted Answer · 2016-02-24 16:27:30Z

UPDATE and Answer:
I modified my approach and went back to the original source data which was not part of a pivot table and was able to use a simple match formula between the 2 data sources. So, my original data looked like this:

+----------------+---------+--------+--------------+
|      Gtin      |  Brand  |  Name  | TaxonomyText |
+----------------+---------+--------+--------------+
| 00030085075605 | brand 1 | name 1 | cat1         |
| 00041100015112 | brand 2 | name 2 | cat2         |
| 00041100015099 | brand 3 | name 3 | cat3         |
| 00030085075608 | brand 4 | name 4 | cat4         |
+----------------+---------+--------+--------------+

I had another sheet containing the data I needed to match to in this format:

+----------------+---------+
|      Gtin      |  Brand  |
+----------------+---------+
| 00030085075605 | brand 1 |
| 00041100015112 | brand 2 |
| 00041100015098 | brand 3 |
| 00030085075608 | brand 4 |
+----------------+---------+

I created a new column in my source sheet and used a if error match formula:

=IFERROR(IF(MATCH(A14,data_to_match!$A:$A,0),"yes",),"no")

Then copied this formula down for every row, about 75K rows which very quickly added a yes or a no.

+----------------+---------+---------+--------+--------------+
|      Gtin      | matched |  Brand  |  Name  | TaxonomyText |
+----------------+---------+---------+--------+--------------+
| 00030085075605 | yes     | brand 1 | name 1 | cat1         |
| 00041100015112 | yes     | brand 2 | name 2 | cat2         |
| 00041100015098 | no      | brand 3 | name 3 | cat3         |
| 00030085075608 | yes     | brand 4 | name 4 | cat4         |
+----------------+---------+---------+--------+--------------+

The final step was to just filter for Yes values and I had all the data that I needed.

My mistake was going to a pivot table first which put the data in a very funky format causing me to have to do a transpose, which wasn't really necessary. Hopefully this can help others....

Collectives™ on Stack Overflow

Excel VBA to transpose set of rows if value exists in another column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related