Extract data to table in new worksheet

RobSA · Nov 11, 2015

Hi Folks,

I hope you can assist.

I have two spreadsheets (MSR07112015 & Report). My data rest in the first and I wish to create a "dynamic" table in the Report, that is when the data changes in the first spreadsheet, the reported data will change.

I have tried using the INDEX function and get close to my objective but the table does not update.

The key in the first spreadsheet is the Due status and the table must be generated for data that is due.

The file is a bit large to load at 1.4Mb

Nebu · Nov 12, 2015

Hi:

Use pivot tables based on the your first spread sheet go to the design tab and format your pivot based on what you need.

Thanks

RobSA · Nov 16, 2015

Thanks Nebu -is that however my only choice ?

Nebu · Nov 16, 2015

Hi:

Without seeing your workbook I won't be able to suggest you anything, I believe you can achieve this using formulas as well.

Thanks

RobSA · Nov 18, 2015

HI Nebo,

The file i have is 1.4 Mb big and thus difficult to post here

Nebu · Nov 19, 2015

Hi:
Try the following:

Save your file in binary format(.xlsb extention), if that is not working try to create an exact replica of your data at a smaller scale and try to upload that file here.

Thanks

RobSA · Nov 19, 2015

HI Nebo, Please find the xlsb file attached

Nebu · Nov 19, 2015

Hi:

I could not make out what you want here, the table you points with an arrow is already having formulas. Is that the table you want to make dynamic? What do you meant by values shown in red and black followed by the letters?

Thanks

RobSA · Nov 19, 2015

Hi Nebo, Thanks for you response. The values in the table as indicated by yourself can be replaced. They were my attempts to get the process to work.

In providing explanation: The numbers that are listed in the column "Submittal Numbers to be returned by 77 Construction Company" (i.e 017-B, 026-O,044-A etc)(30 off) are to be listed numerically in the table below in the Column "No. Off".

Once that is done the date relating to that unique number is to appear in the date column. Then finally the text for the corresponding unique number (i.e 017-B) which is found in the "LOG" must appear in the description column.

Without having to much experiance in excel I have got the tables to populate automatically and thus once we get the above corrected I am sure the table in question will populate dynamically.

Thank you or your support.

Nebu · Nov 19, 2015

Hi:

I have pulled the values from "Submittal Numbers to be returned by 77 Construction Company" to the "No. Off" column. I am not sure on the logic you used for rest of the columns.I guess if it is based on the "No. Off" column it will be fairly easy for you now.Or else let me know and also explain the logic for rest of the columns

Note: The formula I have used is an array formula.

Thanks

RobSA · Nov 19, 2015

Hi Nebo,

The idea is correct. However the numbers are separated by "/" so the grouping should not occur in this desired list. It should be that there be one number per line.

These numbers are brought form the two columns in the "Log" sheet, B11 & AH11. using the concatenate function provide the number in column BD11.

Secondly the numbers listed in this table include, need to list numerically.

Once this is achieved I am sure we could use Lookup or match to complete the remainder of the table.

Nebu · Nov 19, 2015

OK, this is getting complicated for a formula solution, but can be easily achieved through a basic macro. Here is the solution.

Code:

Sub test()

Application.ScreenUpdating = False

Dim arr
Dim i As Integer, j As Integer, cnt As Integer

cnt = 66

For i = 12 To 37
    arr = Split(Range("H" & i), " / ")
        For j = LBound(arr) To UBound(arr)
            Range("C" & cnt) = arr(j)
            cnt = cnt + 1
        Next
Next

Application.ScreenUpdating = True

End Sub

I have included this code on the report tab, click on the run button you will get the desired output from column C66. I guess you will be able to populate rest of the column using simple vlookup.

Thanks

RobSA · Nov 20, 2015

Hi Nebu,

This looks awesome.

I like what you have done in terms changing the value into an integer.

When I use the search function it allows me to find the value and can perhaps reduce the error I am getting with the Vlookup function, returning a #N/A.

Can the macro include for a sort function? That is sort the values from smallest to greatest?

Thanks for your hard work -it is appreciated

Nebu · Nov 20, 2015

Hi:

Use the following code

Code:

Sub test()

Application.ScreenUpdating = False

Dim arr
Dim i As Integer, j As Integer

cnt% = 66

For i = 12 To 37
    arr = Split(Range("H" & i), " / ")
        For j = LBound(arr) To UBound(arr)
            Range("C" & cnt) = arr(j)
            cnt = cnt + 1
        Next
Next
Range("C66:C100").Sort Key1:=Range("C66"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.ScreenUpdating = True

End Sub

This will sort data for you. I will not be able to comment on why you are getting N/As without seeing what you have done.

Thanks

RobSA · Nov 20, 2015

Hi Nebu,

Thanks for helping me resolve this matter -the macro works perfectly.

In terms of the #N/A i think it would be best to continue that discussion under another post.

http://chandoo.org/forum/threads/values-keep-changing-to-n-a.26584/#post-158860

When I search for the value 017-B in the "LOG" worksheet using the Find option it comes back saying the value could not be found.

Using the same spreadsheet you have been working on will perhaps make it easier for you to appreciate.

It is probably why i get a #N/A response.

Nebu · Nov 22, 2015

Hi:

Faseeh is already on with this problem. I am sure he will be able to sort it out for you.

Thanks

RobSA · Nov 23, 2015

Hi Nebu,
Thank you for the feedback.
I have made a change to the macro so as to work for two tables.
The macro works except that the sorting is not acurate on both tables. For example the number 017_B is not coming before the number 006_D
I think it would be good to build in a clear range function so that the table first clears before the new data is applied.

Nebu · Nov 23, 2015

Hi:

That is because I have not give data option to treat alphabets like numbers while sorting, coz I was not sure how you wanted the sorting to be done.

Try this
change the code

Code:

Range("C66:C100").Sort Key1:=Range("C66"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

to

Code:

Range("C66:C100").Sort Key1:=Range("C66"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,DataOption:=xlSortTextAsNumbers

Thanks

RobSA · Nov 23, 2015

Thanks Nebu.

I will try it as requested.

Is it necessary to clear the range or does it occur automatically?

RobSA · Nov 23, 2015

Hi Nebu,

I have made the change and an error occurs , stating that the named argument could not be found. The debugger opens and highlights "DataOptions:=" which seems like th latest adjustment we have made.

Nebu · Nov 23, 2015

Hi:

My Bad it should be dataoption1:=xlSortTextAsNumbers, but ideally if you sort ascending, 006_D should come before 017_B because 17 is higher than 6.

Thanks

RobSA · Nov 23, 2015

Hi Nebu,

Thanks - I have made the adjustment and the error is averted however the change does not happen as planned.

Code:

Sub test()

Application.ScreenUpdating = False

Dim arr
Dim i As Integer, j As Integer

cnt% = 71

For i = 17 To 42
    arr = Split(Range("H" & i), " / ")
        For j = LBound(arr) To UBound(arr)
            Range("C" & cnt) = arr(j)
            cnt = cnt + 1
        Next
Next

Range("C71:C106").Sort Key1:=Range("C71"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Dataoption1:=xlSortTextAsNumbers

Application.ScreenUpdating = True

Call test2

End Sub
Sub test2()

Application.ScreenUpdating = False

Dim arr
Dim i As Integer, j As Integer

cnt% = 113

For i = 17 To 42
    arr = Split(Range("P" & i), " / ")
        For j = LBound(arr) To UBound(arr)
            Range("C" & cnt) = arr(j)
            cnt = cnt + 1
        Next
Next

Range("C113:C122").Sort Key1:=Range("C113"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Dataoption1:=xlSortTextAsNumbers

Application.ScreenUpdating = True

End Sub

Perhaps you can see something that should not be.

Nebu · Nov 23, 2015

Hi:

I am not sure where you are applying the subroutine test2. Is it in the same tab?
if not you will have to put the test2 subroutine in the tab where the data is, else it is working fine at my end.

Thanks

RobSA · Nov 23, 2015

Hi Nebu,

The subroutine is for the table below on the same tab, correct

Nebu · Nov 23, 2015

Hi:

Try the following file , it working fine, you may have to change the cell references as per your file.

Thanks

Extract data to table in new worksheet

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Attachments

Member

Excel Ninja

Attachments

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Attachments