• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract data to table in new worksheet

RobSA

Member
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
 
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
 
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
 
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
 
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
 
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.
 
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
 

Attachments

  • RH_DTS_LOG_IN.xlsb
    821.9 KB · Views: 2
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.
 
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
 

Attachments

  • RH_DTS_LOG_IN.xlsb
    823.7 KB · Views: 3
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
 
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
 
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

W
hen 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.
 
Hi:

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

Thanks
 
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.
 
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
 
Thanks Nebu.

I will try it as requested.

Is it necessary to clear the range or does it occur automatically?
 
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.
 
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
 
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.
 
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
 
Hi:

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

Thanks
 

Attachments

  • RH_DTS_LOG_IN.xlsb
    825.1 KB · Views: 1
Back
Top