Merge Bill Of Material Columns
Dec 5, 2009
I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:
U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID
Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.
View 9 Replies
ADVERTISEMENT
Feb 9, 2010
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
View 14 Replies
View Related
May 14, 2014
I would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:
I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).
Parent Item
Item
QTY
Total QTY
A1234
B1111
5
[URL] ...
I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)
View 3 Replies
View Related
Jul 24, 2009
Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
Would a macro make the task more automated?
View 9 Replies
View Related
Mar 31, 2014
I have two columns, which have different length and in which there can be three types of data:
The data also in the first and the second column, so it is an old data.Second is, that the data is in the first column, but not in the second, so it is old, but it was solved.The third type is, when the data is in the second column, but not iin the first, so it is new data.
My task is to regularly update the list (so I have to merge the new and old columns), to see in which conditions the data is, but not to delete any of them (only the recurring, so if it is old, then it must not be listed two times).
View 1 Replies
View Related
Feb 7, 2009
I have a need to get the data from columns A and B and transfer to C but one line from A and second line from B and this successively until the end of A and B. They will have the same number of lines.
View 3 Replies
View Related
Jan 26, 2013
How I can merge two columns together and keep the data in both columns?
I have an address issue when the data was converted to excel it put the numbers part of the address in column "C" and the street name in column "D".
View 2 Replies
View Related
Sep 12, 2008
i am trying to create a macro that merges two columns in excel.
View 9 Replies
View Related
Dec 29, 2006
I have made an extremely simple chart of bills in Excel. I want it to show each day how many days it is until the monthly due date of each bill. If next to the column of bill names I make a column filled in with single numbers to represent what date of the month each bill is due, can I make a SIMPLE formula to put in the next column that generates the number of days it is until the bill is due?
View 9 Replies
View Related
Feb 15, 2014
I'm currently working with the "Original"sheet shown on the attached file. From this, you will see that there is generic information contained in columns A:D then columns containing a month date and the associated FTE figure.
What I'm trying to do is as follows:
-Insert two columns after the "Investment Role" column, with column headers of "Period" and "FTE"
-Then, for each month column for each record, create a new row one under the other, copy the values in columns A:D and paste them into the newly created row,
-Then move the Date value for each month and the associated FTE figure and put these in the "Period" and "FTE"columns.
I've attached a file showing the 'Original' dataset and 'Desired Outcome' to look at.
Although the values in all of the fields may change month on month, and the number of rows may change, the number of columns will remain the same.
Merge Columns Test.xls
View 5 Replies
View Related
Jul 9, 2009
I'd like to perform a merge of value between columns D and E.
So if D9's value = 2 and E9's value = 15
then the result will be
E9=215
I've tried the following but I haven't gotten it to work.
View 7 Replies
View Related
Mar 22, 2012
I am trying to make a bill of materials using excel. The material is 10' sticks of pipe. What I want to accomplish is have a column (column A) with the lengths used in inches and have it show me the total amount of pipe used keeping in mind that once you hit 120" you have to start a new stick of pipe.
For example: If I used 40 inches I have 80 inches left over... But if column A read (40", 90", 60") I need it to know that the 40" will be cut from one stick, the 90" will need to be cut from a new stick, and the 60" can be cut from the left over of the 40" cut.
View 1 Replies
View Related
Nov 20, 2007
I am trying to do parts forecasting.
My customer makes 6 different widgets, all of which use a different number of the same parts (tabA ~ tabZ)
For example, model A has the following:
1 x tabA
2 x tabB
0 x tabC
4 x tabD.
I have a matrix which shows how many of the different parts are in each model. The part number goes down the rows and the model being produced goes across the columns. So this goes from A10:G:21
Now, I have to do a forecast for the next year and I trying to figure out the best way to do it and have each months totals of each part. My forecast has the model numbers going down the rows and the months going across the columns. This is from A1:G7
The way I used to do it is have a line for every part and manually multiply the monthly total by each models per unit and it ends up being a formula that gets longer and longer.
January needs for tabA are (B2*B11)+(B$3*C11)+(B$4*D11) ...
February needs for tabB are (B2*B12)+(B3*C12)+(B4*D12)...
When they have 20 distinct models it gets difficult. Also, there may be mid-season changes to the bill of materials that make it difficult to change.
View 9 Replies
View Related
Jul 23, 2014
I have two columns that are the same field, both Middle Name. The first column does not have every single row filled and the second column has every row the first column does not have and vice versa. I want to merge them to one filled column.
View 2 Replies
View Related
Aug 14, 2009
I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.
View 5 Replies
View Related
Dec 19, 2012
So I have several columns of data that have a location and then some numbers after it in additional columns. I need to be able to sort it so that all locations that are in both location columns are sorted first, and then any locations that are in one column but not the other column follows. Also the data associated with each column that follows the location needs to remain next to the location. Many times the columns will be of highly disportionate lengths too.
I've attached an HTML table so you can get an idea of what I am looking at, except what I'm dealing with is like.... hundreds of cells long.
I want to be able to turn this:
HTML Code:
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
<tr>
<td>A</td>
[Code].....
And in this 6 row example, I'd actually only be interested in the 4 letters that were in both columns. If it makes it easier, I would be okay with discarding the last rows, but would rather just have them sorted at the end, so I could check if....for example there were two different spellings of the same thing, and they should actually be included.
View 3 Replies
View Related
Nov 12, 2007
I have been scanning web forums for days and have not found a solution to my question. I have found similar, but unfortunately am not clever enough with Excel VBA to adapt.
I have two columns of data which I want merged into rows (a) by survey number and (b) by time slots. The data I have is similar to:
Survey No.....Results
12345...........9am-12pm
12345...........3pm-6pm
12345...........No answer
56789...........6am-9am
56789...........12pm-3pm
56789...........6pm-8pm
56789...........10pm-12am
.....................
View 12 Replies
View Related
Nov 14, 2008
I've got two columns:
A, B
abc, def
qwe, rty
asd, fgh
zxc, vbn
And I need to bring these together in one column so it looks like this
C
abcdef
qwerty
asdfgh
zxcvbn
View 2 Replies
View Related
Nov 18, 2011
My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.
Example of what I have so far:
Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub
View 1 Replies
View Related
Jun 7, 2012
I use the following code to merge all data a "master sheet", but I want to take the data and put it next to each other on the "master sheet", not under. For example, my range of data is "A1:D15" and I want to copy from all sheets. So my first paste would be A1:D15, then the next should be E1:H15.
I would like the code to find the used range, like it does currently.
Code:
Sub Merge()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
[Code]....
View 1 Replies
View Related
Sep 27, 2013
I've found macro, which merge rows with the same data
Example:
BEFORE:
january
1st
january
2nd
[Code] ........
Macro:
Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("a1", Cells(Rows.Count, "a").End(xlUp))
[Code] .....
But i need use the same procedure for columns, it means:
BEFORE
A
B
C
D
E
F
january
january
january
february
february
february
AFTER
A
B
C
D
E
F
january
february
I've tried modified macro marked above, but without success...
View 3 Replies
View Related
Oct 26, 2013
I have 13 different worksheets with the same header columns, i would like to merge into one creating new work sheet. All the data in column A should keep on adding on the column A from all the worksheet and from column N going across. I have more than million records on each sheet
I have dropped the file on [URL]... for example
View 8 Replies
View Related
Aug 7, 2008
i have a table looking something like this
----A--------------B---------------C----------------D-------------
1---Destination-----Country_Code---Area_Code------ Price_Per_Minute
2---Armenia--------99--------------58, 59, 566------0.098
3---Jordan---------96-------------79,78,77,2--------1.023
4---UK-------------44-------------------------------0.50
5---UK-------------44-------------79,78-------------0.48
what i actually need is to turn this table into 3 columns table, the first column is the destination, the second one is the destination code which consists of the concatenation of the country_code and the area_code columns, and the third column will be the price_per_minute, the main problem is that the Area Code column contains many values and each value should be attached to the country_code and then i need to create more rows for each country, so the my table should look like this:
----A--------------B---------------D-------------
1---Destination----Code-------Price_Per_Minute
2---Armenia-------9958-------0.098
3---Armenia-------9959-------0.098
4---Armenia-------99556------0.098
5---Jordan--------9679-------1.023
6---Jordan--------9678-------1.023
7---Jordan--------9677-------1.023
8---Jordan--------962--------1.023
9---UK------------44----------0.50
10--UK------------4479-------0.48
11--UK------------4478-------0.48
View 5 Replies
View Related
Jan 10, 2012
I have dates values in 3 columns.
A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"
A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.
I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.
Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.
View 1 Replies
View Related
Apr 30, 2014
I'm trying to set up a water bill calculator and am having troubles. We have a base rate that we charge for everything under 4000 gals. After that we have an increasing tiered rate. I'm trying to make a form so we can calculate bills accordingly. I don't know how to create an "IF" scenario.
example
If "total usage" is greater than 4001 but less that 8000 multiply by $4.50 per 1000 gal
If "total usage" is greater than 8001 but less that 12000 multiply by $4.75 per 1000 gal
If "total usage" is greater than 12001 but less that 16000 multiply by $5.25 per 1000 gal
If "total usage" is greater than 16001 but less that 20000 multiply by $5.50 per 1000 gal
If "total usage" is greater than 20001 and over multiply by $5.75 per 1000 gal
Is there an easy way to do this?
View 8 Replies
View Related
Mar 12, 2014
I have 2 columns full of information feeding from 2 different sources on our internet database
I would like to collect this information into 1 column to create a drop down list
As these are expanding tables feeding from the internet I don't think copy and paste into one column will work (I'm not sure about this but I would prefer a formula to avoid any issues).
View 4 Replies
View Related
May 19, 2014
I have a large volume of data and i need to manage and organize somehow.
Example:
A B C D E
CHRIS AN204 2005 Apple Green
CHRIS AN204 2005 Apple Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green
This should look like this
A B C D E
CHRIS AN204 2005 Apple Green, Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green
I manage somehow to find a VBA code that will merge my rows, but it's not working as it should. It's losing data.
Also in a cell it can happen to have more than 255 characters.
View 1 Replies
View Related
Apr 13, 2008
I have got a wordlist in worksheet "original" which looks like:
Before: [Code] .....
I need a macro which merges the columns B,C,D,E and F depending on if there are duplicates in Column A or not. If there are one two or more duplicates in column A,then those should be deleted and only one of them should remain in column A. The members of deleted duplicates in column B,C,D,E and F should be merged together. No duplicates should be made by the process of merging. Each member in column B,C,D,E and F has to be unique. The results are supposed to be put in worksheet "new".Columns B,C,E and F should be merged through signe "/". And Column D should be merged through signe ",".
After: [Code] ......
The macro must be able to deal with very large lists. biger than 200 000 words in column A
Here is the excel file containing the example : excel file
View 14 Replies
View Related
Apr 13, 2008
i have got a wordlist in worksheet "original" which looks like:
Before:
View 14 Replies
View Related
Oct 30, 2013
My data is in column A , Column B and Column C, where are column C has a uniuqe value for certain rows.
Based on these uniques value, column A and Column B should be merged to singe Row without loosing data.
MY Data:
Source address
Destination address
Destination Port
Application
IP Protocol
192.168.1.2
192.168.250.10
53
dns
udp
[Code] .......
Result expecting is :
Source address
Destination address
Destination Port
Application
IP Protocol
[Code] .......
View 7 Replies
View Related
