Hello. I need a little help for traspose row items (with value > 0 or not null) form a data table into columns, and create excel file for each row.
Here is my main excel file:
I filter it for each row that “total_com” >“0” to get items ftom “00056”, “00057” etc and what i want is to create an excel file with two columns having the header A1: " COD" and B1: “CANT” and to get the values as in this pic:
Each File must be saved as an excel named as in the main table “company_name” having just one sheet.
Looking forward for your answer,
Thank You
Hy. So far i managed to create individual excel files (one sheet two rows) but i need to transpose this two rows into columns. The way to do that is to read again the excel, but i consider that it must be a more elegant solution directly from the data table to write the rows into columns in the excel file only for row items > 0 (or filter it before to be written). So it would be a real help if you already know the way. Thank you.
Hi Mihai,
Not sure how elegant this can get, I’d stick to the basics, boring and high performance stuff. I wouldn’t even use Filter Data Table for those >0.
Which is, load the initial data into a Data Table, go with For Each Row, use a temporary Data Table to build the smaller per-row set and then, like a full simpleton, test first column for zero, get company name from column 1, whenever you find something in the other columns, get the header and the value and add it to the temp Data Table. After processing the row, Write CSV with the temporary data table with company name as file name, and let For Each Row iterate till end.
I don’t see much room for significant improvements over the above. Even if there was a transpose DT activity, you would’ve still needed to iterate to validate/clean stuff so… just keep it old-school, IMHO.
In general, look for whatever you can do to minimize interactions with Excel (those suck and are costly). Ideally, 2 Excel steps only:
get the the data as Data Table with Read Range, process it as Data Table and
only at the do end do a Write Range with the result DT.
Hy Cornel.
I have already done as you suggested so far, You are right about minimize excel interactions. This is the reason why i am looking for a way to use Data Table all the way and in the end to write range as a excel. The problem is that after processing the data table, i get a two rows range. Is there a way to write them into columns? The solution i use now is to write range as excel and use invoke vba or send hot keys to transpose the rows into columns, but i consider it not to be a good idea, it involves time and is fragile.
ashokkarale
Anil_G
ppr
shyamala_shyamu
Josh_James
m-takeda1
Shahabaz
harshika.10732998
sharazkm32