Problem with extracting data from text file to excel table

[bigpappi23]

Hi Guys,

I have a text file which consists of the following columns:

[TABLE="width: 320"]
<TBODY>[TR]
[TD]UPC CD</SPAN>
[/TD]
[TD]DESCRIPTION</SPAN>
[/TD]
[/TR]
[TR]
[TD]------</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD] MX BERRY</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 10</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5322</SPAN>
[/TD]
[TD]GRAPE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5676</SPAN>
[/TD]
[TD] APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5677</SPAN>
[/TD]
[TD] MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5679</SPAN>
[/TD]
[TD] MMJTG MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5684</SPAN>
[/TD]
[TD]MMJTG APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1D</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15070</SPAN>
[/TD]
[TD]STRAWBERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15072</SPAN>
[/TD]
[TD]ZERO MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15436</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]LEM LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1K</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14573</SPAN>
[/TD]
[TD]LEMON LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14574</SPAN>
[/TD]
[TD]FRUIT PUNCH PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14575</SPAN>
[/TD]
[TD]BERRY BLAST PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14579</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15034</SPAN>
[/TD]
[TD]STRWBRRY LEMONADE</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15456</SPAN>
[/TD]
[TD]WHITE CHERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15989</SPAN>
[/TD]
[TD]A MELON PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]TROP MANGO PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1L</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14565</SPAN>
[/TD]
[TD]BLAST - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14566</SPAN>
[/TD]
[TD]FRUIT PUNCH - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14713</SPAN>
[/TD]
[TD]PA GRAPE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]PA ORANGE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Y</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]MXBER</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Z</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1868</SPAN>
[/TD]
[TD]BERRY BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1875</SPAN>
[/TD]
[TD] TROPICAL BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4888</SPAN>
[/TD]
[TD]FRUIT PUNCH</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4891</SPAN>
[/TD]
[TD]STRWBRY PASSION</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5362</SPAN>
[/TD]
[TD] PINEAPPLE ORG</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 320"]
<TBODY>[TR]
[TD]UPC CD</SPAN>
[/TD]
[TD]DESCRIPTION</SPAN>
[/TD]
[/TR]
[TR]
[TD]------</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD] MX BERRY</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 10</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5322</SPAN>
[/TD]
[TD]GRAPE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5676</SPAN>
[/TD]
[TD] APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5677</SPAN>
[/TD]
[TD] MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5679</SPAN>
[/TD]
[TD] MMJTG MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5684</SPAN>
[/TD]
[TD]MMJTG APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1D</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15070</SPAN>
[/TD]
[TD]STRAWBERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15072</SPAN>
[/TD]
[TD]ZERO MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15436</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]LEM LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1K</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14573</SPAN>
[/TD]
[TD]LEMON LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14574</SPAN>
[/TD]
[TD]FRUIT PUNCH PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14575</SPAN>
[/TD]
[TD]BERRY BLAST PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14579</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15034</SPAN>
[/TD]
[TD]STRWBRRY LEMONADE</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15456</SPAN>
[/TD]
[TD]WHITE CHERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15989</SPAN>
[/TD]
[TD]A MELON PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]TROP MANGO PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1L</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14565</SPAN>
[/TD]
[TD]BLAST - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14566</SPAN>
[/TD]
[TD]FRUIT PUNCH - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14713</SPAN>
[/TD]
[TD]PA GRAPE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]PA ORANGE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Y</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]MXBER</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Z</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1868</SPAN>
[/TD]
[TD]BERRY BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1875</SPAN>
[/TD]
[TD] TROPICAL BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4888</SPAN>
[/TD]
[TD]FRUIT PUNCH</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4891</SPAN>
[/TD]
[TD]STRWBRY PASSION</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5362</SPAN>
[/TD]
[TD] PINEAPPLE ORG</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

then i will export the data into an excel table. I'm able to do that but my problem is exporting the Major Brand. Because i'm required to create another column and in that column will be the Major Brand column and each UPC code needs to have a major brand associated with it.

For example:
UPC Code 5322,5676,5677,5679,5684,5685 is under Major Brand: 1D. So the Major Brand column needs to be filled with 1D for each UPC code. (Hope i explained it clearly :D)

can you help me out on how to code this one? please provide the code.

thank you very much

 

[hiker95]

bigpappi23,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You may want to check your screenshot - it contains two complete duplicate areas beginning with the following:

Excel 2007
	A	B
1	UPC CD	DESCRIPTION
2	------	------------------------------
3	15929	MX BERRY
4	15929	------------------------------
Sheet1

And, can we have a screenshot (manually formatted by you) for what the results should look like?

 

[bigpappi23]

Hi Hiker95,
I'm using a PC/MS Excel 2010 and the screenshot i provided is from a raw text file. I have already worked on removing the duplicates when the data inputs in excel.
here's the screenshot of what i wished it to look like in excel:

[TABLE="width: 624"]
<TBODY>[TR]
[TD]UPC[/TD]
[TD]Major Brand[/TD]
[TD]Description[/TD]
[TD]Cost per Unit[/TD]
[/TR]
[TR]
[TD]015929[/TD]
[TD]10[/TD]
[TD] BERRY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05322[/TD]
[TD]1D[/TD]
[TD] GRAPE PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]02945[/TD]
[TD]1D[/TD]
[TD] APPLE PET[/TD]
[TD]                         6.75[/TD]
[/TR]
[TR]
[TD]05677[/TD]
[TD]1D[/TD]
[TD] ORANGE PET[/TD]
[TD]                       10.43[/TD]
[/TR]
[TR]
[TD]06401[/TD]
[TD]1D[/TD]
[TD] MIX BERRY PET[/TD]
[TD]                         7.58[/TD]
[/TR]
[TR]
[TD]05684[/TD]
[TD]1D[/TD]
[TD] APPLE PET[/TD]
[TD]                         6.63[/TD]
[/TR]
[TR]
[TD]05685[/TD]
[TD]1D[/TD]
[TD] ORANGE PET[/TD]
[TD]                         6.63[/TD]
[/TR]
[TR]
[TD]015070[/TD]
[TD]1K[/TD]
[TD] STRAWBERRY PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]03935[/TD]
[TD]1K[/TD]
[TD] BERRY PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]015436[/TD]
[TD]1K[/TD]
[TD] ORANGE PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]015437[/TD]
[TD]1K[/TD]
[TD]LIME PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]014573[/TD]
[TD]1L[/TD]
[TD]LEMON LIME PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014574[/TD]
[TD]1L[/TD]
[TD] FRUIT PUNCH PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014575[/TD]
[TD]1L[/TD]
[TD] BERRY BLAST PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014579[/TD]
[TD]1L[/TD]
[TD] ORANGE PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]015034[/TD]
[TD]1L[/TD]
[TD] STRWBRRY LEMONADE[/TD]
[TD]                         5.37[/TD]
[/TR]
[TR]
[TD]015456[/TD]
[TD]1L[/TD]
[TD]WHITE CHERRY PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]015989[/TD]
[TD]1L[/TD]
[TD] MELON PET[/TD]
[TD]                         5.51[/TD]
[/TR]
[TR]
[TD]016418[/TD]
[TD]1L[/TD]
[TD]TROP MANGO PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]014565[/TD]
[TD]1Y[/TD]
[TD] BLAST - PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014566[/TD]
[TD]1Y[/TD]
[TD] PUNCH - PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014713[/TD]
[TD]1Y[/TD]
[TD] GRAPE - PET[/TD]
[TD]                         6.19[/TD]
[/TR]
[TR]
[TD]014714[/TD]
[TD]1Y[/TD]
[TD]ORANGE - PET[/TD]
[TD]                         6.48[/TD]
[/TR]
[TR]
[TD]05138[/TD]
[TD]1Z[/TD]
[TD].MXBER[/TD]
[TD]                              -  [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
[TABLE="width: 624"]
<TBODY>[TR]
[TD]UPC[/TD]
[TD]Major Brand[/TD]
[TD]Description[/TD]
[TD]Cost per Unit[/TD]
[/TR]
[TR]
[TD]015929[/TD]
[TD]10[/TD]
[TD] BERRY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05322[/TD]
[TD]1D[/TD]
[TD] GRAPE PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]02945[/TD]
[TD]1D[/TD]
[TD] APPLE PET[/TD]
[TD]                         6.75[/TD]
[/TR]
[TR]
[TD]05677[/TD]
[TD]1D[/TD]
[TD] ORANGE PET[/TD]
[TD]                       10.43[/TD]
[/TR]
[TR]
[TD]06401[/TD]
[TD]1D[/TD]
[TD] MIX BERRY PET[/TD]
[TD]                         7.58[/TD]
[/TR]
[TR]
[TD]05684[/TD]
[TD]1D[/TD]
[TD] APPLE PET[/TD]
[TD]                         6.63[/TD]
[/TR]
[TR]
[TD]05685[/TD]
[TD]1D[/TD]
[TD] ORANGE PET[/TD]
[TD]                         6.63[/TD]
[/TR]
[TR]
[TD]015070[/TD]
[TD]1K[/TD]
[TD] STRAWBERRY PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]03935[/TD]
[TD]1K[/TD]
[TD] BERRY PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]015436[/TD]
[TD]1K[/TD]
[TD] ORANGE PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]015437[/TD]
[TD]1K[/TD]
[TD]LIME PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]014573[/TD]
[TD]1L[/TD]
[TD]LEMON LIME PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014574[/TD]
[TD]1L[/TD]
[TD] FRUIT PUNCH PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014575[/TD]
[TD]1L[/TD]
[TD] BERRY BLAST PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014579[/TD]
[TD]1L[/TD]
[TD] ORANGE PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]015034[/TD]
[TD]1L[/TD]
[TD] STRWBRRY LEMONADE[/TD]
[TD]                         5.37[/TD]
[/TR]
[TR]
[TD]015456[/TD]
[TD]1L[/TD]
[TD]WHITE CHERRY PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]015989[/TD]
[TD]1L[/TD]
[TD] MELON PET[/TD]
[TD]                         5.51[/TD]
[/TR]
[TR]
[TD]016418[/TD]
[TD]1L[/TD]
[TD]TROP MANGO PET[/TD]
[TD]                         5.27[/TD]
[/TR]
[TR]
[TD]014565[/TD]
[TD]1Y[/TD]
[TD] BLAST - PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014566[/TD]
[TD]1Y[/TD]
[TD] PUNCH - PET[/TD]
[TD]                              -  [/TD]
[/TR]
[TR]
[TD]014713[/TD]
[TD]1Y[/TD]
[TD] GRAPE - PET[/TD]
[TD]                         6.19[/TD]
[/TR]
[TR]
[TD]014714[/TD]
[TD]1Y[/TD]
[TD]ORANGE - PET[/TD]
[TD]                         6.48[/TD]
[/TR]
[TR]
[TD]05138[/TD]
[TD]1Z[/TD]
[TD].MXBER[/TD]
[TD]                              -  [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

For example:

the UPC CD 15929 is under major brand 10. in the major brand column it will show 10 and for the UPC CD 5322 to 5685 it will show 1D in the major brand column.

 

[hiker95]

bigpappi23,

The text displays (wrapped inside CODE markers) you are showing do not give us a good idea where the data actually is in the worksheet cells, rows, and, columns.

I would like to have a screenshot of the raw data after you pull it into a worksheet.

And, I would like to have another screenshot of what the results (manually formatted by you) should look like.


To post small screen shots try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[bigpappi23]

Hi hiker95

Please see the file on this link
https://app.box.com/s/iet1hje0rayqv8zcbmof
the desired output is in the filename:Project(expected output)
https://app.box.com/s/aei6b32vjnl0aeu1nzyj

 

[hiker95]

bigpappi23,

Thanks for the two workbooks.

We seem not be able to understand each other.


In my last reply I had asked for the following:

1. I would like to have a screenshot of the raw data after you pull it into a worksheet.

2. And, I would like to have another screenshot of what the results (manually formatted by you) should look like.


There is no correlation between the data in the following two screenshots?????

Where are the Major Brand's coming from?????

Excel 2007
	A	B	C	D	E
10		UPC	Description	Cost per Unit
11		015929	BERRY
12		05322	GRAPE PET	-
13		02945	APPLE PET	6.75
14		05677	ORANGE PET	10.43
15		06401	MIX BERRY PET	7.58
16		05684	APPLE PET	6.63
17		05685	ORANGE PET	6.63
18		015070	STRAWBERRY PET	5.27
19		03935	MIX BERRY PET	5.27
20		015436	ORANGE PET	5.27
21		015437	LEM LIME PET	5.27
22		014573	LEMON LIME PET	-
23		014574	FRUIT PUNCH PET	-
24		014575	BERRY BLAST PET	-
25		014579	ORANGE PET	-
26		015034	STRWBRRY LEMONADE	5.37
27		015456	WHITE CHERRY PET	5.27
28		015989	PA MELON PET	5.51
29		016418	MANGO PET	5.27
30		014565	MT BLAST - PET	-
31		014566	FRUIT PUNCH - PET	-
32		014713	GRAPE - PET	6.19
33		014714	ORANGE - PET	6.48
34		05138	MXBER	-
35
Project

Excel 2007
	A	B	C	D	E
10		UPC	Major Brand	Description	Cost per Unit
11		015929	10	BERRY
12		05322	1D	GRAPE PET	-
13		02945	1D	APPLE PET	6.75
14		05677	1D	ORANGE PET	10.43
15		06401	1D	MIX BERRY PET	7.58
16		05684	1D	APPLE PET	6.63
17		05685	1D	ORANGE PET	6.63
18		015070	1K	STRAWBERRY PET	5.27
19		03935	1K	MIX BERRY PET	5.27
20		015436	1K	ORANGE PET	5.27
21		015437	1K	LEM LIME PET	5.27
22		014573	1L	LEMON LIME PET	-
23		014574	1L	FRUIT PUNCH PET	-
24		014575	1L	BERRY BLAST PET	-
25		014579	1L	ORANGE PET	-
26		015034	1L	STRWBRRY LEMONADE	5.37
27		015456	1L	WHITE CHERRY PET	5.27
28		015989	1L	PA MELON PET	5.51
29		016418	1L	MANGO PET	5.27
30		014565	1Y	MT BLAST - PET	-
31		014566	1Y	FRUIT PUNCH - PET	-
32		014713	1Y	GRAPE - PET	6.19
33		014714	1Y	ORANGE - PET	6.48
34		05138	1Z	MXBER	-
35
Project_Results

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[bigpappi23]

BUMP

Hi hiker95,

My apologies

the raw data, which is the text file is in the first thread. That's the exact format of the text file where the raw data will be coming to be put into my excel table. In the last thread. the first screenshot is the one that i have made. I already been able to pull the data into my excel table. Now as for the expected result. I have added the " Major Brand" column. What i would like to happen is, I would like to extract the major brand in to the table column for each UPC associated with it. If you would kindly take a look at the text file and the expected output. the major brand only occurs below each associated UPC's and what i would like to happen is the major brand would be extracted for each UPC associated.


thank you so much

 

[hiker95]

bigpappi23,

Is the following what the raw data looks like after you bring it into the workbook?

Excel 2007
	A	B	C	D
1	UPC	Major Brand	Description	Cost per Unit
2	15929	10	BERRY
3	5322	1D	GRAPE PET	-
4	2945	1D	APPLE PET	6.75
5	5677	1D	ORANGE PET	10.43
6	6401	1D	MIX BERRY PET	7.58
7	5684	1D	APPLE PET	6.63
8	5685	1D	ORANGE PET	6.63
9	15070	1K	STRAWBERRY PET	5.27
10	3935	1K	BERRY PET	5.27
11	15436	1K	ORANGE PET	5.27
12	15437	1K	LIME PET	5.27
13	14573	1L	LEMON LIME PET	-
14	14574	1L	FRUIT PUNCH PET	-
15	14575	1L	BERRY BLAST PET	-
16	14579	1L	ORANGE PET	-
17	15034	1L	STRWBRRY LEMONADE	5.37
18	15456	1L	WHITE CHERRY PET	5.27
19	15989	1L	MELON PET	5.51
20	16418	1L	TROP MANGO PET	5.27
21	14565	1Y	BLAST - PET	-
22	14566	1Y	PUNCH - PET	-
23	14713	1Y	GRAPE - PET	6.19
24	14714	1Y	ORANGE - PET	6.48
25	5138	1Z	.MXBER	-
26
Sheet1

If the above is correct, in reference to cells, rows, and, columns, then what is the worksheet name?

 

[bigpappi23]

nope, the one that you posted is my expected out put. the raw data comes from a text file(i.e notepad). then from that notepad file. i will extract the data that i needed to my excel table.

 

[hiker95]

bigpappi23,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.