You are here:

Excel/Data aliening with criteria

Advertisement


Question
Hello Tom,
Hope you are fine. You are a grate guy. You helped me a lot.

I have a worksheet with some data, which i would like to restructure which is compatible with our software where we load our data. given data is as below ...


ORI   DEST   RBD   NORMAL DIS   SPECIAL DIS

MY   AU   F   8%          12%
MY   AU   A   5%   
MY   AU   P   0%   
MY   AU   J   10%   
MY   AU   C   10%          8%
MY   AU   D   10%   
MY   AU   Y   6%          11%
MY   AU   B   5%   
MY   AU   H   5%   
MY   NZ   F   8%          12%
MY   NZ   A   5%   
MY   NZ   P   0%   
MY   NZ   J   10%   
MY   NZ   C   10%          8%
MY   NZ   D   10%   
MY   NZ   Y   6%          11%
MY   NZ   B   5%   
MY   NZ   H   5%   
CN   PH   F   8%          12%
CN   PH   A   5%   
CN   PH   P   0%   
CN   PH   J   10%   
CN   PH   C   10%          8%
CN   PH   D   10%   
CN   PH   Y   6%          11%
CN   PH   B   5%   
CN   PH   H   5%   

in this data, you will find for RBD F,C and Y we have Special discount on travel along with normal discount.

When we load data in our software we, insert Special discount data first for each origin and destination.
and our final data looks like as below

ORI   DEST   RBD   NORMAL DIS   SPECIAL DIS
MY   AU   F          12%
MY   AU   C          8%
MY   AU   Y          11%
MY   AU   F   8%   
MY   AU   A   5%   
MY   AU   P   0%   
MY   AU   J   10%   
MY   AU   C   10%   
MY   AU   D   10%   
MY   AU   Y   6%   
MY   AU   B   5%   
MY   AU   H   5%   
MY   NZ   F          12%
MY   NZ   C          8%
MY   NZ   Y          11%
MY   NZ   F   8%   
MY   NZ   A   5%   
MY   NZ   P   0%   
MY   NZ   J   10%   
MY   NZ   C   10%   
MY   NZ   D   10%   
MY   NZ   Y   6%   
MY   NZ   B   5%   
MY   NZ   H   5%   
CN   PH   F          12%
CN   PH   C          8%
CN   PH   Y          11%
CN   PH   F   8%   
CN   PH   A   5%   
CN   PH   P   0%   
CN   PH   J   10%   
CN   PH   C   10%   
CN   PH   D   10%   
CN   PH   Y   6%   
CN   PH   B   5%   
CN   PH   H   5%   

for sample purpose i only provided 3 origin and destination. however we have more than that.
it's very time consuming to copy that row and placed on top.

Is there any formula or macro for this.

Hope; i able to frame my question properly , else if you want i will send the sample worksheet to your mail ID.

Thanks
Rakesh

Answer
Rakesh,

If you want to put in the same three rows just adjusted for ORI and DEST combinations, then a macro could be used to do that.  

Another way would be to generate all the added rows using a formula and insert them at the top - then sort your data.  The macro is probably the easiest after it is written.

If you want to send the file, send it to twogilvy@msn.com

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.