Help Extracting Data- Probably VBA for looping?

**flexchick** · 01-27-2018, 06:44 PM

Hi everyone,

I am very rusty at my Excel skills, and it seems like I may need to use VBA with this one. I have a spreadsheet that is about 4000+ rows of data: Names, Access Levels, and card numbers. The problem is that there is one name associated with multiple rows of data, and it isn't consistent. Basically I need to take this data, and have the person's name, access levels, and card number in column format so that I can concatenate and put into CSV format.

In the attached workbook, you'll see the raw data sheet, and then the desired result page. Is there a way to use the vlookup on a merged first cell with multiple adjacent rows, and dump that data into multiple columns like second example?

Any assistance is greatly appreciated. I'm trying to help out a friend, and am now stuck on trying to figure it out because I want to.

**jindon** · 01-27-2018, 07:12 PM

If you upload a workbook with the sheets what you have and what you want as a result will help.
Picture doesn't help usually.

Attach a sample workbook. 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.

**sandy666** · 01-27-2018, 07:29 PM

you can substitute char(10) with a space - char(32) in column Access Level then concatenate columns

edit: forget it , you're using merged cells

**flexchick** · 01-27-2018, 07:29 PM

Thanks- this is my first post here so I appreciate the advice.

**flexchick** · 01-27-2018, 07:32 PM

Thanks, but I know how to concatenate- it's getting the data into the columns in the first place that I need help with. Merged first column with multiple rows attached. I uploaded an example workbook since I did a crappy job of explaining what I needed. Thank you anyway!!

The setup for the raw data was not my idea- I can unmerge cells, but that creates different problem or having blank cell associated with the data I need.

**shg** · 01-27-2018, 08:08 PM

Please Login or Register  to view this content.

**sandy666** · 01-27-2018, 08:16 PM

or something like this with PowerQuery

**jindon** · 01-27-2018, 08:25 PM

Try

Please Login or Register  to view this content.

**flexchick** · 01-27-2018, 08:31 PM

Thank you. Each name and card number have merged cells, and the access levels vary in rowsize (anywhere from 1 to 4 access levels). How do I tell it to look up the name, and then copy the rows next to it to paste into columns on new worksheet, and then move to the next name? Can you tell me what I'm putting as the ranges? It would be so much easier if I could send the actual spreadsheet...lol.

**sandy666** · 01-27-2018, 08:37 PM

To whom you are talking?

**flexchick** · 01-27-2018, 08:57 PM

Sandy666, how did you get the merged name fields and accesses lined up in that power query?

**sandy666** · 01-27-2018, 09:04 PM

here is M code:

Please Login or Register  to view this content.

Data - show queries - double click on Table 1 - Menu - Advanced Editor
or look at Applied Steps in Query Settings (after open Table 1)

Edit:
If you want more see:

**flexchick** · 01-27-2018, 09:23 PM

Thanks bunches. For some reason, I'm not seeing Power Query in my Data tab- I have the regular query, but my build of 2016 must be different. There are about 4,000+ lines of data, and it looks like you manually created each of the merged columns? I already transposed the table, it was just getting it into that final format. It would be quicker to just cut and paste than create a query for each name?

**jindon** · 01-27-2018, 09:27 PM

flexchick

Have you tried my attachment in #8?

**sandy666** · 01-27-2018, 09:37 PM

Power Query in Ex2016 is called: Get&Transform

gt.jpg

Maybe your Excel is 365 not 2016

with a little different layout

read more about PowerQuery / Get&Transform from links above

**flexchick** · 01-27-2018, 09:47 PM

Thank you jindon- that one does what I need. Aside from making sure the sheet names are the same in .xlsm coding, what else will I need to change to get it to work on my larger dataset?

**flexchick** · 01-27-2018, 09:48 PM

Hi Sandy- I have 2016- and it shows as "Get and Transform" in my version. Thanks for all of the helpful information!

**sandy666** · 01-27-2018, 09:51 PM

You are welcome

Why MS changed this name - I don't know

but IMHO PowerQuery sounds much better than Get&Transform, eg. use Power (Query) Luke !

**jindon** · 01-27-2018, 09:51 PM

Originally Posted by flexchick

Thank you jindon- that one does what I need. Aside from making sure the sheet names are the same in .xlsm coding, what else will I need to change to get it to work on my larger dataset?

It should work in any size of the data.
If you have problem, I need to see your workbook.

**flexchick** · 01-27-2018, 10:00 PM

Thanks Jindon- can I private message you with this?

**jindon** · 01-27-2018, 10:09 PM

PM sent...............

**flexchick** · 01-27-2018, 10:26 PM

Thanks- reply sent. Let me know what you think!

**jindon** · 01-27-2018, 10:37 PM

OK, I see
Just replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

And see how it goes.

**flexchick** · 01-27-2018, 11:32 PM

Jindon, it worked beautifully. Thanks again. That was so very helpful!!

Thank you also to Sandy, and anyone else replying. This is a wonderful resource.

**jindon** · 01-27-2018, 11:39 PM

You are welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Re: Help Extracting Data- Probably VBA for looping?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extracting and transposing data by looping

[SOLVED] Extracting data from table using VBA looping

[SOLVED] Extracting Data from Table by Looping

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions