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

Text to Column Split

Hi sir,
I need a macro to split the description by delimiter and match each attribute in every row and every column. i have attached the example file for your reference.

Note:
* Normally when split the description in input data by text to column by separator semicolon, then attribute+value combination split like in example file
* But i Need Desired Output Data like in Example file, ie when split the data by delimiter, output should be, match each attribute in every row and every column.
 

Attachments

  • Split.xlsx
    16 KB · Views: 10
jeyachandran
How to 'guess' - which attributes are used?
... or should those find out first?
If there (in normal use) are more than those ten rows data then that could take ... time.
Do You write only about Sheet1? ( Are those others like disinformation? )

... and this seems to be almost same with Your one of previous thread:
 
hi sir,
this post entirely different from previous thread: VBA Concatenate,
input data for reference only

* input data: i split out the Description (column B) by delimiter semicolon

* Output Data: output Result i want.

* So consider Sheet 1 is Input, Sheet 2 to be Output

* no need for those attribute find out first but i need proper alignment only

* If want to add more columns no problem.

ie, I need misaligned (input) data into structured alignment data (Output) when run the macro
 

Attachments

  • Split_Ref.xlsx
    14.8 KB · Views: 3
jeyachandran
How to 'guess' - which attributes are used?
If cannot know, then ... this would take much longer time!

Where do You need those Sheet1's other that A- & B-columns?
... and after B-column has 'done' - now need that.

Too many things would happen - if You someway get Your 'input' other way!

Your previous thread is basically same!
 
At cell B15 of Sheet2 is a table that can be updated by right-clicking and choosing Refresh.
Is that the kind of output you're looking for?
 

Attachments

  • Chandoo46000Split_Ref.xlsx
    25.2 KB · Views: 3
Yes sir.

This output is good

and also i need to automate (macro) the task, Could you help me to Run the Task from Sheet 1-Column B (row 2 to last row) and result might be in sheet 2.

Thanks..:):)
 
The source data is whatever the table Table1 extent covers; if you replace/update/add/remove data in the Table, it usually resizes itself to match, but if it's not quite right you can resize the table by dragging the grab handle at the bottom right of the table.
Also the button at cell C1 of Sheet1 of the attached will try to do that and refresh the result table; it runs this macro:
Code:
Sub blah()
Range("Table1").ListObject.Resize Range("Table1").cells(1).CurrentRegion.Resize(, 2)
Range("Table1_2").ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
If you select the result table in its entirety you can drag it where you like, or cut and paste it to another sheet.
 

Attachments

  • Chandoo46000Split_Ref v_02.xlsm
    32.6 KB · Views: 5
In Sheet2 of the attached (which is the same as the last one I attached but with a new function), in cell A26 is a formula:
=blah3(Sheet1!A4:B13)
I strongly suspect that for your version of Excel, this will not only needs to be array-entered (using Ctrl+Shift+Enter instead of plain Enter to commit the formula to the sheet) but needs to be committed to the sheet while a range is selected being the same number of rows as you have source data+1 row, and as many columns as there are 'Attributes' in your data (which can vary). In this case it's 19. Better to start off with too many columns where the rightmost ones will contain errors showing that you've entered the formula into too many columns (the same applies to the bottom rows if you've selected too many rows).
Once you've got the results you can eliminate the formula by copy,paste-special|Values in situ.

There's at formula at cell A14 which referes to the same range, but because that range is an Excel table it uses its name:
=blah3(Table1)
 

Attachments

  • Chandoo46000Split_Ref v_02.xlsm
    40.8 KB · Views: 1
Back
Top