Free Microsoft Excel 2013 Quick Reference

[Newbie] Converting Data to Tree Structure

Hi,

I have created an excel spreadsheet with data pertaining to our Family Tree. Since I need to Display the final data in a Family Tree Structure, and with Indian Font, none of the Genealogy software available on the Net are of help.

For my data, there are about 10 Columns, of which I've created 2 columns as Self ID & Parent ID, through which I am trying to create a tree structure, and trying to create a visual output using the returned values. The Data I have is for about 9 generations, and hence 9 levels of nesting. Can anybody help with this project? I am a newbie to Excel Programming, although can be a fast learner.

Thanking in advance,

Ajay.


I have imported data that is formatted as General. I need it formatted as Date. The form is 20010207 whcih represents February 7, 2001. The Format, Cells option to format as a Date returns #############, etc.

I need to be able to convert/format to a Date type in VBA in order to do some calculations with the dates (for example, use one date and sort remainders for those within 60 days). This process will be done in VBA code.

I cannot get the cell values recognized as Date types in any way.

Any help would be appreciated.

I converted my data to labels. I can't get the first 0 in the zip code does
not show or print.

Hello All,

Happy Holidays to all!
Need to fit my raw data (Raw Data Sheet) to fit my table template.
Need to convert my daily data to weekly to fit to my table template.

Please see attached file for reference.

Thanks in advance!

Hey all -

I have another issue similar to the last one I had regarding converting data exported into Excel into columnar data. Rylo provided a solution for me for my previous problem with this, but now, I've got a similar report with data moved around some and for the life of me, I can't figure out how to adjust his code to make it pull in the data I want.

What I'm trying to do is take the questions under Auto and Home Calls that are located in column 4 and fill in their answers in column 8 on the generated data worksheet. I've attached a sample workbook that has the code I received from Rylo that works perfectly on another book where the questions I'm searching for are in column 1, but again, I can't get it to look at the question in column 4 and give me the answer to that question in column 9. Everything else works with it.

Can someone point me in the right direction and tell me what I'm doing wrong here?

Thanks Again!
Nick

Example:
Product | Date | Sales
-----------------------
A | Jan94 | 200
A | Feb94 | 300
A | Mar94 | 400
A | Apr94 | 500
A | May94 | 600
A | Jun94 | 700
A | Jul94 | 800
A | Aug94 | 200
A | Sep94 | 300
A | Oct94 | 400
A | Nov94 | 500
A | Dec94 | 600
B | Jan94 | 200
B | Feb94 | 300
B | Mar94 | 400
B | Apr94 | 500
B | May94 | 600
B | Jun94 | 700
B | Jul94 | 800
B | Aug94 | 200
B | Sep94 | 300
B | Oct94 | 400
B | Nov94 | 500
B | Dec94 | 600

How can I convert it to:
Product | Jan94 | Feb94 | Mar94 | Apr94 | May94| Jun94 | Jul94......
---------------------------------------------------------------------
A | 200 | 300 | 400| 500 | 600 | 700 | 800 .....
B | 200 | 300 | 400| 500 | 600 | 700 | 800 .....

Does Excel contains certain function instead of retype all data?

Thanks a lot.

HI,
I am frequently converting data from one format into a usable row format, usually involving dates listed about 5 across and then repeated below.

the number of sections changes each week. I would like a looping macro that copies and pastes the data from the rows below to the columns across - see example sheet.

a really neat option would be if the data could then be separated by months - same sheet or separate sheets doesn't matter.

thank you!

Deal all,

from long time i am faceing a problem to convert some data to date.

please have a look in my attachment. there i like to conver B columon to Date format how can i change.

Hello to all

I was trying to input an event macro posted on this page that allows to force uppercase data entry using an Event Macro. The macro works very good when you type or paste in a single row, but when I tried to paste several cells, it did not converted the info to uppercase.

This is the code that I used:


	VB:
	
 Range) 
     
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub 
     
    On Error Resume Next 
    If Not Intersect(Target, Range("A4:G5003")) Is Nothing Then 
        Application.EnableEvents = False 
        Target = UCase(Target) 
        Application.EnableEvents = True 
    End If 
    On Error Goto 0 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I was hopping if somebody could tell me how to make this macro to convert data to uppercase when pasting several cells in differen columns and/or rows.

Thanks in advance for the help!

Good Afternoon. Glad I found this forum.

Here is my problem. I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).

The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.

That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.

Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.

The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet

Hello.

First, I've read Excel help and worked through several on-line
tutorials on the Microsoft website in regards to XML support in Excel
2003. And, have searched web and Usenet looking for clues. So, please
forgive if this has been asked and answered; I didn't find it if it
has. Thanks!

I've got an Excel spreadsheet containing data.

I'd like to be able to export that as XML. Not via the "save as" an
XML spreadsheet method - too much extraneous data in the resultant
output. Too difficult to parse out the unwanted stuff. Microsoft
doesn't even recommend that method.

What I'd like to do is what Microsoft recommends anyway: Define an "XML
Map" and so on.

Problem: All the documentation seems to indicate that you can define
the map (either by supplying your own xml schema or by letting Excel
create one for you), and then you have to IMPORT xml data into your
spreadsheet where it is then stored when you save it.

Well, my data's ALREADY in a spreadsheet. Is there ANY way to get
Excel to accept THAT DATA as being xml data without me having to (a)
re-key it all into a new spreadsheet or (b) export it out via whatever
method and convert it to "well formed" xml BY HAND so I can re-bring it
back in again?

That really, really seems kind of ludicrous!

I've tried mapping my xml map to the existing data and it will happily
accept JUST THE ONE ROW as being xml data (and subsequently exports
just that one row).

I've tried making my data into an Excel "list" (2003 feature), and then
mapping the xml map to that. Seems to accept it. You see the fields
in the tree in the XML Source window jump around as you click on cells
in different columns in the data, so it seems to recognize the data as
being mapped in it's entirety. But, when you do to the Data menu, XML,
Export, it says "Cannot save or export XML data. The XML maps in this
workbook are not exportable."

Sigh. Come on. Surely many, many, many folks are in this position.
Don't tell me Microsoft went to all the trouble to add the level of XML
integration that they did to Excel, and negelected to provide some way
- any way - for folks to make use of their existing, legacy data?
(Without having to go through a needless conversion)

Answers, suggestions, tips welcome!!!! In advance, Thank You!

p.s. If you can answer the above, perhaps you can also answer this?
Say I have my XML map defined, have data that happily believes it's xml
in the worksheet, can export to "well formed" xml and so on.

Say I want to add a new column? One would think that Excel would then
allow you to define a new node in the XML Source tree. Nope. You can
add the column, but Excel refuses to accept that new column as xml.
Any suggestions? A procedure? Am I just not getting something
obvious? Again, thank you!

I have a work sheet in excel with data in 8 different columns. By
using a macro I can view this data with drop down lists in a user
form, like with auto filters in the user form. I would like to show
the data in another way and would like to know if it is possible to
build a tree structure in the user form instead?

Example:

Cars
BMW
Mercedes
Audi
Bicycles
Crescent
Monark
BMX

Instead of choosing Cars and Bicycles in drop down lists I would like
a tree structure to expand when clicking on the word Cars (BMW,
Mercedes, Audi then appears beneath) for instance in the user form.

The functionality is similar to having groups in the work sheet except
I would like it to be presented in a user form instead.

Grateful for all the help I can get!

/Malin

Hello.

First, I've read Excel help and worked through several on-line
tutorials on the Microsoft website in regards to XML support in Excel
2003. And, have searched web and Usenet looking for clues. So, please
forgive if this has been asked and answered; I didn't find it if it
has. Thanks!

I've got an Excel spreadsheet containing data.

I'd like to be able to export that as XML. Not via the "save as" an
XML spreadsheet method - too much extraneous data in the resultant
output. Too difficult to parse out the unwanted stuff. Microsoft
doesn't even recommend that method.

What I'd like to do is what Microsoft recommends anyway: Define an "XML
Map" and so on.

Problem: All the documentation seems to indicate that you can define
the map (either by supplying your own xml schema or by letting Excel
create one for you), and then you have to IMPORT xml data into your
spreadsheet where it is then stored when you save it.

Well, my data's ALREADY in a spreadsheet. Is there ANY way to get
Excel to accept THAT DATA as being xml data without me having to (a)
re-key it all into a new spreadsheet or (b) export it out via whatever
method and convert it to "well formed" xml BY HAND so I can re-bring it
back in again?

That really, really seems kind of ludicrous!

I've tried mapping my xml map to the existing data and it will happily
accept JUST THE ONE ROW as being xml data (and subsequently exports
just that one row).

I've tried making my data into an Excel "list" (2003 feature), and then
mapping the xml map to that. Seems to accept it. You see the fields
in the tree in the XML Source window jump around as you click on cells
in different columns in the data, so it seems to recognize the data as
being mapped in it's entirety. But, when you do to the Data menu, XML,
Export, it says "Cannot save or export XML data. The XML maps in this
workbook are not exportable."

Sigh. Come on. Surely many, many, many folks are in this position.
Don't tell me Microsoft went to all the trouble to add the level of XML
integration that they did to Excel, and negelected to provide some way
- any way - for folks to make use of their existing, legacy data?
(Without having to go through a needless conversion)

Answers, suggestions, tips welcome!!!! In advance, Thank You!

p.s. If you can answer the above, perhaps you can also answer this?
Say I have my XML map defined, have data that happily believes it's xml
in the worksheet, can export to "well formed" xml and so on.

Say I want to add a new column? One would think that Excel would then
allow you to define a new node in the XML Source tree. Nope. You can
add the column, but Excel refuses to accept that new column as xml.
Any suggestions? A procedure? Am I just not getting something
obvious? Again, thank you!

Greetings.

I have data in a column showing various Z heights for different X and Y locations as such:

X 0.100
Y 0.100
Z -0.001
X 1.100
Y 0.100
Z -0.002
X 2.100
Y 0.100
Z 0.000
X 3.100
Y 0.100
Z 0.002

This needs to be converted to a table format to enable 3D plotting as such:

0.1 1.1 2.1 3.1
0.1 -0.001 -0.002 0.000 0.002

Where the values in the top row (0.1, 1.1, 2.1, 3.1) are the X values and the values in the leftmost column are the Y values. Data listed at each point in the table is the corresponding Z height.

I have ~100 data points so doing this manually will be quite tedious. Is there some easy way to convert the list to a table format? Ultimately I want to graph out the values and my understanding is that I need this table style format to the data to be able to use any of the Surface type charts.

Thanks for any help.

Hey guys,

I don't know if this can be done in excel, but please take a look at the image and let me know if i have a excel sheet with data, i want it to finally be arranged in a tree structure as shown in the image, and columns to the right with data in it, as shown in the image.

Is it possible using VBA?

THanks,
Ken
PS: I have Darkened in black the names.

Hi, I can see that this has been answered on a recent thread (Convert from table to a list) but I'm not confident/capable enough to get the macro to work for me.

I have data which I've inherited which is in a table format, so for example
Month Level 1a 1b 2a (26 columns)
January 5 0.5 0.5 0.5
January 2a 0 0 0
Feb 2b 3 7 6
Feb 2b 2 2 0
Feb 2b 0 5 0
March 2b 20 35 0
March 2b 0 4 0
April 2b 6 6 4
January 2b 0 4 0
January 4 3 18 0

I'd like to change this so that the data becomes pivotable by using the column headings to create a list for each data point (all the numbers) i.e. each "entry" will multiply by the 26 column headings I have, but will only have four columns of data on it:

January >> 5 >> 1a >> 0.5
January >> 5 >> 1b >> 0.5
January >> 5 >> 2a >> 0.5
January >> 2a >>1a >>0
January >> 2a >> 1b >>0

Can anyone help? If its relevant, the data sits between A1:AB191

I have some data given to me showing:

Column A Date of item arrival to site
Column C Number of days on site (ie Column E - Column A)
Column E Date of item depature from site
(Other columns not needed)

There are about 520 rows with each row showing a single item passing through the site. The data spans across about 1 year.

What I need to do is to plot a chart which shows:
X-Axis: Date
Y-Axis: Number of items onsite that day

I guess I will need to manipulate the data first but not sure how to convert it to show what was onsite each day.

Any ideas?
Thanks

I am trying to to convert numeric data to words. Example: 5.5 to five and
one half.
I recall reading somewhere that this can be done with a macro of some type,
unfortunatly I don't recal where I read this.

I believe that to analyse my data using pivot tables and pivot charts,
my raw data must be formatted as a "list". Unfortunately it isn't. Can
anyone help me to reorganise it please?

I'm analysing sales data for several products, by monthly sales over
three years. The data is sent to me in the following format: the column
headers are product, year, sales in Jan, sales in Feb, sales in Mar.....
sales in Dec. I want a pivot chart with the product in the page field,
sales figures up the side and months along the bottom, with a different
line (data series) for each year. I think to do this I need to convert
my data so that the columns headers are product, year, month and sales,
and apply the pivot table to that.

How can I quickly transfer the sales figures in the columns "Jan
sales", "Feb sales" etc so that they each appear on a separate row that
states the month in one column and the sales for that month in another?
Do I actually need to do it like this?

Any help gratefully received!

--
Freezerbird
------------------------------------------------------------------------
Freezerbird's Profile: http://www.excelforum.com/member.php...o&userid=35286
View this thread: http://www.excelforum.com/showthread...hreadid=550706

I have a set of data that are numbers stored as text. CLicking on the little
green tab enables me to convert the text in the cell into a number. All The
data in the column starting from a certain row and ending at a certain row
are numbers stored as text. Also, it's a very long long long list. Is there a
shortcut to converting all the data to numbers without having to click the
green tab for each cell or having to highlight all the cells in the column
(only to zip past the last row) and go through cell properties?

Thanks

I've got a form with several calculated values, including time value of money
formulas and "If" statements. I need to convert it to a data access page so
that the form can be used across an intranet. The problem is that when i
convert those formulas don't seem to want to convert and there is no
expression builder in DAPs.

Any help would be greatly appreciated.

Thanks,

Jason

how to convert multi decimal place data in colums to 2 decimal places incl.
zeros

I need to convert data in various cells to one cell of text. The cells contains numbers and need to be converted in tact as text - example column A 000123 column B 000345 converted to column C 000123000345.

Converting Data Type could recognize these date format like 2004.12.31,
20041231 and will convert them to the locale format in your computer. It
could convert any data to Text, Number or Date&Time.

The Cell Lens of Locked&Unlocked change the background color of cells
according to their lock/unlock state: the locked cells to gray, the unlocked
cells to blue. Assist has also offered a Quick Lock button to lock the
selected range at once and a Quick Unlock button to unlock immediately. Now
you are very clear about which cells are locked and which cells are unlocked.
Just cancel this Cell Lens, the background color will be restored.

Cell Lens of Data Type render and change the background color of cells
according to their data type: Text to yellow, Number to blue, Date and Time
to green. Just cancel this Cell Lens, the background color will be restored.

If you merge some cells in Excel, just the data of first cell could be
maintained and the data of other cells will be destroyed! Safety Merge of
AddinTools Assist to merge the selected cells and maintain all data. Smart
Unmerge to unmerge the selected cells and the data in every cell will be
split into several cells!

Virtual Screen Rulers helps to align the rows and columns in a big complex
worksheet.

Please download and try it free from http://www.addintools.com.

Cell Lens Of Data Type render and change the background color of cells
according to their data type: Text to yellow, Number to blue, Date&Time to
green ... Just cancel this Cell Lens, the background color will be restored.
Converting Data Type could recognize these date format like 2004-12-31,
20041231 and will convert them to the locale format in your computer. It
could convert data of cells to Text, Number or Date&Time type.

The Cell Lens Of Locked&Unlocked render and change the background color of
cells according to their lock/unlock state: the locked cells to gray, the
unlocked cells to blue. Assist has also offered a Quick Lock button to lock
the selected range at once and a Quick Unlock button to unlock immediately.
Now you are very clear about which cells are locked and which cells are
unlocked. Just cancel this Cell Lens, the background color will be restored.

If you merge some cells in Excel, just the data of first cell could be
maintained and the data of other cells will be destroyed! Safety Merge of
AddinTools Assist to merge the selected cells and maintain all data. Smart
Unmerge to unmerge the selected cells and the data in every cell will be
split into several cells!

Virtual Screen Rulers help you to align the rows and columns in worksheet.
It will draw a colored horizontal line under the active row in a worksheet,
and draw a colored vertical line on the right of the active column.