Solution to Problem: Excel formula to match word in cell to similar header titles and copy column data into another worksheet.

Excel formula to match word in cell to similar header titles and copy column data into another worksheet.

I have a very large list of data contained in one worksheet called "data", the columns are titled 2002 profit, 2002 debt, 2002 capital, etc.
and then it goes on for each year 2003 profit, 2003 debt, 2003 capital, etc.
I would like to enter the word "profit"
into cell a1 of worksheet called "analyse"and all columns of data containing the word profit woud be pulled into the worksheet.
i.e.
2002 profit, 2003 profit, 2004 profit, etc.
columns would appear, if the word "capital" was typed then 2002 captital, 2003,
captital, 2004 captital columns would be pulled into the worksheet.
Is there cell formulas that would work for this? the challenge is that i would like to avoid macro's or VBA.

Anwsers to the Problem Excel formula to match word in cell to similar header titles and copy column data into another worksheet.

Download Error Fixer (Free)

BargeBerger,
Say on the data sheet you have the column headers 2002 profit, 2002 debt, 2002 capital, 2003 profit, 2003 debt, ..., 2004 capital in cells A1:I1, with the relevant data below.
Then on the analyse sheet in cell A1 you enter a year, while in cells B1:D1 you have the column headers profit, debt, capital.
Then in cell B2 have this formula, and drag-fill across and down:
=INDEX(data!$A2:$I2,MATCH($A$1&" "&B$1,data!$A$1:$I$1,0))
 
Adjust the ranges to suit how many columns you really have on the data sheet.
___________
Regards, Tom

To check for Windows updates

1. Open Windows Update by clicking the Start button Picture of the Start button, clicking All Programs, and then clicking Windows Update.
2. In the left pane, click Check for updates, and then wait while Windows looks for the latest updates for your computer.
3. If any updates are found, click Install updates. Administrator permission required If you are prompted for an administrator password or confirmation, type the password or provide confirmation.

Recommended Method to Fix the Problem: Excel formula to match word in cell to similar header titles and copy column data into another worksheet.:

 

 

How to Fix Excel formula to match word in cell to similar header titles and copy column data into another worksheet. with SmartPCFixer?

1. You can Download Error Fixer here. Install it on your system. When you open SmartPCFixer, it will perform a scan.

2. After the scan is finished, you can see the errors and problems which need to be repaired.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.

Related: Can t download Windows Media Centre 12 for Windows 7 Professional asus driver download Help there is an error with the top task bar on all my windows Windows 7 Fuzzy buttons www asus com is coverXP Pro compatible with Windows 7 asus mainboard driver download Registry edit Hkey current user Software Microsoft Windows CurrentVersion Explorer CD Burning Drives asus driver Starting display problem Your computer did not shut down properly Windows is collecting information asus drivers Windows 8 in Outlook 2013 Clicking e mail address from body of email gives error asus motherboard drivers Windows Recovery Disk Black screen White mouse cursor asus drivers windows-update,Is Windows7 Compatable With Microsoft 2010 Home & Student? Excel Keeps Crashing With Files Created In XP And New Files Created In 2010 Home & Student,Number Items In String, Start Over If Criteria Change,Cannot Print From Windows Live Mail Or IE8,Red X Instead Of Picture?,Uninstall Windows Fax And Scan
Read More: Troubleshoot:Windows 7 after shutdown will not power off,Troubleshooting:Upgrade from Windows 7 Home Premium to Professional, \"Plugged in, not charging, consider replacing battery\" Error,How Can You Fix - My internet explorer will prompt it had stopped working.?,Troubleshoot:i want to make gmail my DEFAULT email program. HOW????????__ Error,(A) When I try to open "My msn" I get a pop-up saying: the service is temporarly not available. (B) The installation left two problems,Added hyperlink to a row and used it for a week now can't open it anymore,Stopping junk e-mail from accumulating,Share / Access folder using Computer Name instead of IP Address,System restore showing message "System restore could not start"