Horizotal data in EXCEL to Vertical data

Help
1

Hi @badita, @ClaytonM and All,

I have some data in excel like in below image.

IMAGE1
IMAGE11123Ă—211 15.9 KB

From the above excel, I need to get values of Address, City, State, Country etc. and need to put it in another excel like below image.

IMAGE2
IMAGE2877Ă—121 2.74 KB

Can anyone help me on this?

Thanks in Advance!!

2

Hi there @abishek!

I suposse that you have few pages equals to screenshot “Page1”, having the data in rows, and one above the next, so…

You will have to work with 2 DT, the first one for get the data of 1st Excel, and the second one to split the data as you want, i mean, in columns instead of rows.

I would try to keep each value of adress, city, country, etc… (horizontal) and then copy them in each row of the 2nd DT (vertical)… Doing this steps for each adress,city,country, etc…

Maybe there is another easiest way to do it.

Bests!
Pablo

3

If the data always comes in same cells, you can use “Read Range” Activity itself. :slightly_smiling_face:

4

I’m assuming the Page1 Excel has all the information in the first column.

So first thing is you need to know what keywords you are looking for to extract. In this case, you can use basically your column headers that you want. Let us actually store the keywords into a new Data Table with Build Data Table (if you don’t already have an existing spreadsheet. Then, we can use those column headers as your key words.
Build Data Table // for example I'll use table called dtNew

Then, let’s Read Range of the Page1 Excel. —use Excel Scope to avoid file in use

Excel Scope
    Read Range // for example I'll use table called dt1
    Close wb

So with dtNew we can loop through each column to use the header and store each value to an array which we can use in the Add Data Row.

infoArray = dtNew.Columns.Cast(Of String).Select(Function(x) "").ToArray // initialize empty array

For each col In dtNew // TypeArgument as DataColumn
    infoArray( dtNew.Columns.IndexOf(col) ) = dt1.AsEnumerable.Where(Function(r) r(0).ToString.Contains(col.Name) ).ToArray(0).Split({col.Name},System.StringSplitOptions.None)(1).Trim.Split(":"c)(1)

Then, there will be some extra words in the Split method I used, so run it through one more loop to replace any of the key words.

replaceKeys = {"(Please provide street address; a post office box is not sufficen","/PROVINCE"}

For each item in infoArray //TypeArgument String
    For each rKey In replaceKeys
        item = item.Replace(rKey, "").Trim
    For each col In dtNew
        item = item.Replace(col.Name, "").Trim


Add Data Row // use infoArray in the ArrayRow property... Add to dtNew
Write Range

I think that covers most of it. Disclaimer: I did not test any of the above solutions, but hopefully it helps you.

Now, if the Page1 Excel doesn’t have all the data in the first column, then it changes a lot.

EDIT: you might also change everything with .ToUpper to remove possibility of case-sensitivity. :slight_smile:

Regards.

C

3 Likes
5

Awesome @ClaytonM

clap

6

Thank You for your response @ClaytonM…:slight_smile: :smile: :clap: