Link to home
Start Free TrialLog in
Avatar of AMoore
AMoore

asked on

Expand information down with a lot of information

I am trying to create a sheet that I can paste over 200 unique master order numbers that will then pull up sub orders for the master orders. I would like the sheet to automatically expand down to add the additional data as I want to add multiple lookups in the columns after depending on the data pulled. I am pulling the info from multiple other sheets so I will need to do some manipulation either way, but any help would be greatly appreciated. I am planning on creating a third sheet for the automatic lookups but the info comes from 2 other sheets. The lookup value will be copied from one sheet into the new sheet and lookup the info off of a 2nd sheet. The results can have up to 10 distinct identifiers or just 1 and I would like to have the sheet grow down as necessary without having to change anything major each time.
For example,
I am using a master order number to lookup all of the sub orders. I paste the 200 orders into cells A1-A201 and want the sub orders to populate in Column B and if cell A1 has 2 results to display in B1, I would like the results to display in B1 and B2 and the data in cell A2 gets automatically pushed to cell A3. Is this possible?
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Well all is possible to better grasp what you want start by posting a sample data and will take it from there.
gowflow
Avatar of AMoore
AMoore

ASKER

Sorry,
 The attached document shows a quick generic sample of what I am looking for. Columns E and F will be on another sheet, but the gist is here. What I am copying the lookup data from would be constant data from cell 1 to cell 200 and I want it to expand down with empty spaces until all the sub orders are listed in column B. Then the next master order will be listed in the next cell after.
Book1.xlsx
Let me get this right!

1) You hav in book1.xlsx that you just posted Col A and B the way they are when you posted the workbook.
2) You want to 'copy' or a 'macro to copy' data from somewhere else that is pasted in Col E and F
3) As a result you want the workbook that I just posted ???

Let me know.
If yes I need the workbook that contain the data E and F so we can build the macro to do all this automatically in a press of a button.

gowflow
Book1.xlsx
Avatar of AMoore

ASKER

Not exactly what I am meaning.
The data in columns A and B are in a workbook with a lot of other data in more columns. About 30 columns and 17000 rows.
I am looking for only specific master order information out of the 17000 rows.
When I would be pasting the order numbers I need info for, it would be pasted like:
M001
M002
M003
and I would like for it to end up looking like columns E and F in the workbook. I can't post the actual workbooks due to information in them.
Well ...

You want a solution specific then I need data.

You make a copy of your workbook
in this copy change all the sensitive data by something dummy
and post it keeping the data in their exact same columns.

I know it is work but if you want a solution then this is the way
gowflow
Avatar of AMoore

ASKER

How about this.
In this book, sheet1 is the page I would be pulling the information from. Sheet2 is how the information looks from the sheet that I want to get the order I need to look up and sheet3 is how I would like it to look once I copy the info from sheet2. I changed the data and removed any sensitive info. Sheet 1 where I pull the data from is in the columns it needs to be in. Sheet2's info is sent to me and may be in different rows as to why I want to make a separate book of sheet to look the info up.
I hope this helps.
Book1.xlsx
Excellent

Fill me sheet2 as much as you can like you did for sheet1 and I will build the macro that will get you sheet3
these 4 items not enough

I create macro, test and test and when I deliver it is usually 96% of time faultless. This is why I am 'exigeant'
:)

gowflow
Avatar of AMoore

ASKER

OK. I have added a lot more data and did a quick replace on them to make the info unrecognizable. I have added a lot of data to sheet 1 and sheet 2. Sheet 2 is how I would receive it, so before copying it, I would be removing duplicates.
Preferably I would be able to copy and paste it after removing the duplicates exactly in line how it is and excel could expand it down like I shown on page 3.
Avatar of AMoore

ASKER

One more thing,
The data is on a local drive, so I can't really use the sheet I sent you unless I copy all the info and paste it into it. I would prefer to have something that I can change the locations that the data is being pulled from to make it easier.
in the last 2 threads you did not upload any file.

NO PROBLEM

here is my solution try it and put the data you want and see if it is fine. First activate macros and press on the button in sheet2 and check the results in a newly created sheet.

Let me know.
gowflow
MasterandOrders.xlsm
Avatar of AMoore

ASKER

Thanks. I'll have to try it on Monday as I won't be back in the office till then. I appreciate your help and I'll let you know if it works.
Sorry that's what happens when you go fast !!!!

here is the updated code the previous has a bug.

Sub BuildOrders()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim MaxRow1 As Long, MaxRow2 As Long, MaxRow3 As Long, I As Long, J As Long
Dim cCell As Range

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS1 = Sheets("Sheet1")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row
Set WS2 = Sheets("Sheet2")
MaxRow2 = WS1.Range("A" & WS2.Rows.Count).End(xlUp).Row
On Error Resume Next
Set WS3 = Sheets("Results")
If Err <> 0 Then
    Sheets.Add after:=WS2
    Set WS3 = ActiveSheet
    WS3.Name = "Results"
End If
On Error GoTo 0
WS3.Cells.Delete
MaxRow3 = WS3.Range("A" & WS3.Rows.Count).End(xlUp).Row


'---> Create Header
WS3.Range("A1:B1") = Array("Master #", "Order #")
MaxRow3 = MaxRow3 + 1

'---> Build Orders
For I = 2 To MaxRow2
    Set cCell = WS1.UsedRange.Find(what:=WS2.Cells(I, "A"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        For J = cCell.Row To MaxRow1
            If WS1.Cells(J, cCell.Column) = cCell Then
                '---> copy the row to Results
                If J = cCell.Row Then
                    WS3.Cells(MaxRow3, "A") = cCell
                    WS3.Cells(MaxRow3, "B") = cCell.Offset(, -3)
                Else
                    WS3.Cells(MaxRow3, "B") = cCell.Offset(J - cCell.Row, -3)
                End If
                MaxRow3 = MaxRow3 + 1
            Else
                Exit For
            End If
        Next J
        
    End If
Next I

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox MaxRow3 - 1 & " Orders Created in sheet Results.", vbInformation, "Build Orders"

End Sub

Open in new window


Use this newer version. Attached
gowflow
you can simply try the latest workbook now and if it is fine then take your time on Monday anyway it needs adaptation to your code and you will need help on this.

gowflow
Avatar of AMoore

ASKER

I will when I get back to a computer. I am currently on mobile. Thanks.
Avatar of AMoore

ASKER

Thanks. I input the new data you added and it seemed to work fine. Monday I will try to integrate it into my data. I really appreciate the help. I am gonna try and figure out how you did it cause I want to understand it.
ok no problem if u hv questions I will b glad to help.
gowflow
nopoints: updated and merged code fix
MasterandOrders-withcorrections.xlsm
Tks David ! I had forgotten to attach the workbook.
here it is.

gowflow
MasterandOrders-V01.xlsm
Avatar of AMoore

ASKER

I am getting an error when I run the file. I copied the information I am pulling from into sheet 1 and the lookup info on sheet 2. Ran the macro and an error pops up.
It also seems to be missing some of the results that I am looking for (this may be a result of the error). I will attach a bigger spreadsheet to show how much data I am looking through. Just keep in mind, that the amount of data can change very quickly. May go up or down throughout the day.
Capture.PNG
Book1.xlsx
its not the amount of data it is the way columns are !!! this is why I asked for more data. Let me run it on this data will revert in a while
gowflow
Sorry my mistake a small bug !!! here is the updated version with your data and new code in the attached workbook.

Sub BuildOrders()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim MaxRow1 As Long, MaxRow2 As Long, MaxRow3 As Long, I As Long, J As Long
Dim cCell As Range
Dim a

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS1 = Sheets("Sheet1")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row
Set WS2 = Sheets("Sheet2")
MaxRow2 = WS2.Range("A" & WS2.Rows.Count).End(xlUp).Row
On Error Resume Next
Set WS3 = Sheets("Results")
If Err <> 0 Then
    Sheets.Add after:=WS2
    Set WS3 = ActiveSheet
    WS3.Name = "Results"
End If
On Error GoTo 0
WS3.Cells.Delete
MaxRow3 = WS3.Range("A" & WS3.Rows.Count).End(xlUp).Row


'---> Create Header
WS3.Range("A1:B1") = Array("Master #", "Order #")
MaxRow3 = MaxRow3 + 1

'---> Build Orders
For I = 2 To MaxRow2
    Set cCell = WS1.UsedRange.Find(what:=WS2.Cells(I, "A"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        For J = cCell.Row To MaxRow1
            If WS1.Cells(J, cCell.Column) = cCell Then
                '---> copy the row to Results
                If J = cCell.Row Then
                    WS3.Cells(MaxRow3, "A") = cCell
                    WS3.Cells(MaxRow3, "B") = cCell.Offset(, -3)
                Else
                    WS3.Cells(MaxRow3, "B") = cCell.Offset(J - cCell.Row, -3)
                End If
                MaxRow3 = MaxRow3 + 1
            Else
                Exit For
            End If
        Next J
    Else
        a = 1
    End If
Next I

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox MaxRow3 - 1 & " Orders Created in sheet Results.", vbInformation, "Build Orders"

End Sub

Open in new window

gowflow
MasterandOrders-V02.xlsm
Avatar of AMoore

ASKER

So a couple of questions. It seems like I am missing a little data. For example, the results show HJ853738 as having 2 orders. There are actually 3 orders if you go back to sheet 1. any idea why it would be missing this info?
Also, if I then want to do vlookups using other info on the results page, will I need to recreate this each time I run the macro, or will the formulas stay in column c and beyond?
Running it with the new version gets rid of the bug. Thanks. Sorry I forgot to attach it Saturday.
Welll ... we are dragging here all because you don't want to post the workbook that you have. See you are wasting both your time and mine.
I will look into the missing data meantime have a workbook that is EXACTLY like yours (remove sensitive data) and I will be willing to look and adapt to o it. without it sorry can't.

gowflow
Avatar of AMoore

ASKER

Attached is the layout that I am pulling from. The results I am looking for would pull the data in the following order.
Column D  expanded down to get A. Then lookup A for  G - H - I - J - P.  I hope that makes sense.
Avatar of AMoore

ASKER

File didn't attach
Book1.xlsx
ok here is a revised version and the missing was due to your initial data is not sorted (I was mis lead by the small sample you gave) anhow I modified the logic test this version.

Also noticed in Sheet2 that you are repeating the same Master is this normal ??

gowflow
MasterandOrders-V03.xlsm
test the file first
then I will look into the second part if too much will ask you to put a related question as was not part of initial question. If simple then will deal with. But first give me feedback on the last version 03 I have posted and the comments I made.

gowflow
Avatar of AMoore

ASKER

It is normal to get it that way. I will be removing duplicates to take them out as I only need the master and all its subs listed once.
The macro can do this for u !!!
:)
if you want just need to know.
gowflow
Avatar of AMoore

ASKER

I just tested it. Works exactly how I was hoping it would now. I can do the simple things, but if its not too difficult, I would appreciate them added in.
Can I ask, is there a formula that does the lookup and expand down function that you built into the macro? I wouldn't mind expanding my knowledge a little more.
ok here is the version that takes care of removing the duplicates I kept in A the original and in B the unique values for you to verify.
I will be attending your question later (to answer it for sure it is possible but I need to see the file didn't have a chance yet will do once back) as I have a tennis session now.

cu
gowflow
MasterandOrders-V04.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AMoore

ASKER

You are very knowledgeable and I appreciate all of your help.
ok tks your appreciation and glad I could help. Pls feel free to let me know if you need any help by posting a link in here or send me a message.
gowflow