Posts by ivandgreat

Dears,

I would like to create a macro that will list all unique IDs in the RName field (column E), separated by a comma in each cell in column I (output shown in column J), when it filter (one by one) all listed ID mentioned in column H but not included the ID that was used as a criteria in filtering.

br,

Re: 30USD Macro to Count, Update, Search Table

in my first sheet (Ports) all information will come from, then in Sum sheet is a summary from this Ports sheet table.

For example, in Sum sheet table, there is a list of ID (2000,2001,2002, etc), then for each field below will do some computation, lets filter ID 2000 in first sheet (Ports)

• the Cap field will only count the no of ID exist in the with respect to that ID, so ID 2000 will have 16, ID 2001 have 16 also and so on to the other ID, when you filter it out.
• the CapID field will only count the number of listed in RName field with respect to ID itself, when we filter ID 2000 in (ID field) you'll notice that it has 4 items in RName field (M_2000_1, M_2000_2, 2005_1 and 2005_2) but we only needed the ID that has the same as what we look which is ID 2000 (disregard the other characters), that is ID 2000 which is counted as 2 as shown in the table (Sum Sheet)
• the SumCap is much easier, count only the number of listed names exist in RName field, which is counted as 4 (M_2000_1, M_2000_2, 2005_1 and 2005_2)
• the NODepID field will only count the unique ID in RName field but not included the ID itself, as you filter ID 2000 in ID field you'll find that it has 4 item listed in RName field as M_2000_1, M_2000_2, 2005_1 and 2005_2, but should disregard other characters so we come up with duplicate ID for ID 2000 and ID 2005, since ID itself is not included (as we filter only ID 2000) it will counted to only 1 which is ID 2005 as a unique ID.
• in IDs field should show that unique IDs that was mentioned in NoDepID field which is ID 2005 for filtering ID 2000.

The output is on the Sum sheet which is the data in fields "Cap", "CapID", "SumCap", "NoDepID" and "IDs". The only data is on the field ID (2000, 2001, 2002, et). The source data is all in "Ports" sheet that will be using in gathering this information needed for each fields in the "Sum" sheet.

The other sheets,

• SearchID are only be used as a filter in "Sum" sheet, showing the row table with respect to search ID and the IDs in the "IDs" field
  
  
  • For ID 2000, should show ID 2000 as well as ID 2005

  
  

• SearchPorts is the same criteria as "SearchID" sheet but the table source is come from "Ports" sheet.

Hope you'll be able to find a better solution for this.

Re: 30USD Macro to Count, Update, Search Table

Quote from S M C;615795

So what is it that you want the macro to do?

I only wanted to have the output as mentioned in my first post, search the dependent IDs of the search ID as per each ID listed in my sheet.

Re: 30USD Macro to Count, Update, Search Table

Please take a look with the 2nd attachment, "summary".

Re: 30USD Macro to Count, Update, Search Table

any gurus have tried to solve this..

Re: 30USD Macro to Count, Update, Search Table

Quote from S M C;615619

Please clarify

Please see new attached file for more clarification.

Please help me to create a macro for my table.

I have four tables in my attached file as Ports, Sum, and SearchID, SearchPorts sheet, i wanted to have a macro that;

• will update the columns SumCap, NoDepID and IDs in "Sum" sheet. The column "SumCap" will only calculate (sum up) the number of list in column E (RName) @ "Ports" sheet. Column "NoDepID" will count the number of IDs (no duplicate and not included the ID itself) exist in column E (RName). And column "IDs" will only list the IDs that was counted in column "NoDepID"

• in "SearchID" sheet, when i search the ID, i would like only to filter from "Sum" sheet all IDs that was listed in it column "IDs" including the ID that i search, example was shown in my attached file.

• in "SearchPorts" sheet, it will list down the corresponding table from "Ports" sheet that was search in "SearchID", together with the IDs mentioned in column "IDs", example was also shown in my attached file.

br,

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Fine with me, for now i'm just starting to learn some basic codes..

I attached another sample files for your reference.

br,

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Hi,

I just thought of it is really hard to work on this kind of file, could you just help me to have a code to at least look on the "traffic routing" name from the sample files then copy the table below it until the last row of that table and paste it in a separate sheet at the next available row.

A macro to open the all available files in that folder, copy, paste, then automatic with current date.

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Hello again,

Could you please update me about this.

br,

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Hi,

I just notice some source file doesn't have the specified field i've mentioned in my previous sample, some have it some don't have, what will be the best solution for this?

Hope your continue support about this.

br,

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Hello again,

It seems fine but i just forgot to notice something also to be consider,

1. the sheet name is also the same as the file name
2. there was a title name "Save Report" before the table, please check another attachments.
3. Could it be possible instead of opening the file one by one, just open all existing files in specified folder (open, copy, paste and close the file one at a time until it opens all and update the excel file)
4. instead of excel file, open the html as excel, since the table is the same.
5. It is possible to copy all existing table to its specified table name (as sheet name).
6. Please advise, as you seen the attachment.

br,

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Hello again,

I thought of saving it in one file with different sheets (with respect to their table), i attached the sample output that will help you. Also I would like to add that after copying the rows from the source file, it is possible to add the source file name in first column (ex 2012-1) from each row to be able to distinguish each row from which file it came from?

Hope i didn't make it hard for you about this additional request.

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Also, the tables sometimes located in other rows (row 5, row 12, row 23, etc), but still the table fields are the same for all.

The unique name for each table is one row above the table field name, as per my example, Terminal Configuration has 2 table while Traffic Routing has only 1.

I'm planning to look only for this name then start copying the rows (below their field name) with respect to each tables and transfer to much better sheet.

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

How should i pay in PayPal?

Re: 20$ How to Copy Separate tables (with Diff Fields) and transfer to next empty row

Actually the number of rows for each table will be varies from different source file upon running the code, but the field names are the same for all 3 tables.

it will consist of more than 3 tables to max of 10 tables.

Hi,

I'm new in excel programming and I'm seeking to all gurus out there to help me about my excel problem.

I have two or three different table in one single sheet (Sheet1) which is separated to each other from a single row with there respective fields. I want to copy each table without the header and transfer there rows to each respective excel File (XFile1, XFile2, XFile3).

Hope your support about this.

br,